Deluan Quintão 385e75e9a9 perf(db): skip annotation join in CountAll when unused (#5694) ...

* perf(persistence): skip annotation join in CountAll when unused

The Native API list endpoints (/api/song, /api/album, /api/artist) issue a
pagination count on every request via rest.GetAll. CountAll unconditionally
added a LEFT JOIN on the annotation table to a count(distinct id) query. The
join's columns are stripped by count(), but the distinct-over-join forced
SQLite to stream and dedup every row, making the count dominate the request
time on large libraries (e.g. ~190ms cold for 95k songs, seconds for
non-admin users behind the library subquery).

Gate the annotation join: only add it when a filter actually references an
annotation column. The need is detected by rendering the query to SQL and
matching annotation column names as whole words, which covers both named
filters (starred, has_rating) and raw squirrel filters. The column set is
derived from model.Annotations so it tracks schema changes; average_rating is
excluded because it lives on the base table, and word-boundary matching keeps
it from matching the annotation column rating.

Counts are unchanged; only the query plan changes. Unfiltered song counts drop
from ~37ms to ~4ms (warm) on a 95k-song library.

* test(persistence): make annotation-join detection case-insensitive

Address review feedback: SQLite column names are case-insensitive, so a raw
filter using e.g. "RATING" would previously evade the case-sensitive column
regex and wrongly drop the annotation join. Add the (?i) flag (average_rating
stays excluded — the underscore still prevents a word boundary before rating)
and cover it with mixed-case tests. Also make the starred-count assertion an
exact value instead of a range.

2026-06-30 22:50:43 -04:00

8.7 KiB

Raw Permalink Blame History

View Raw