Freegle / Iznik / 5677 / 5677

70%

master: 70%

push

circleci

perf(modtools): UNION ALL per groupid for /modtools/messages (~5000x faster) (#242)

* perf(modtools): speed up /modtools/messages listing via UNION ALL per groupid

`ListMessagesMT` issued one query with `WHERE mg.groupid IN (list)`.  MySQL
falls back to a temporary-table + filesort because the composite
messages_groups(groupid, collection, deleted, arrival) index is ordered per
groupid, not globally — so for a mod of four large groups the query took
~26.7s on prod.

Rewrite each branch (default, searchall, searchmemb) as a UNION ALL over
one subquery per groupid, each with its own backward index scan and LIMIT.
Prod timing drops to ~5–8ms (roughly 5,000x faster).  `SELECT DISTINCT` is
kept only in the users_emails LEFT JOIN branch where duplication is real;
elsewhere (msgid, groupid) uniqueness in messages_groups makes DISTINCT
redundant now that each branch is keyed to one groupid.

Regression test covers a mod with 2 active groups and interleaved arrival
times: first page must be globally DESC-ordered (not grouped by groupid),
and the pagination cursor must yield the remaining messages in order.

Co-Authored-By: Claude Haiku 4.5 <noreply@anthropic.com>

* fix(modtools): deduplicate cross-group messages in UNION ALL listing

A message posted to multiple groups appeared once per UNION branch,
breaking TestListMessagesMTMultiGroupNoDuplicates.  Wrap the UNION ALL in
GROUP BY msgid with MAX(arrival) so cross-posted messages collapse to one
row ordered by their most-recent arrival across all queried groups.

This preserves the original SELECT DISTINCT semantics while keeping the
index-seek + early-termination benefit of the per-group UNION ALL plan.

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>

---------

Co-authored-by: Claude Haiku 4.5 <noreply@anthropic.com>

4511 of 10173 branches covered (44.34%)

Branch coverage included in aggregate %.

2014 of 4221 relevant lines covered (47.71%)

41.05 hits per line