mirror of
https://github.com/navidrome/navidrome.git
synced 2026-08-31 07:30:32 +00:00
1c83f36ddf
The two album list requests reported in discussion #5929 (a 110k-album, two-library instance) were dominated by work that scaled with the whole library instead of the page: - Every native API list request also runs a count for the X-Total-Count header. album had no index covering library_id, so that count scanned the entire table on every request, dragging each row's JSON blobs through the page cache. count(distinct id) additionally built a temp b-tree over the full result set even when the query had no join that could duplicate rows. - A random sort cannot be served by an index, so SQLite evaluated SEEDEDRAND for every matching row and pushed each one, with its library and annotation joins and full column projection, through a sorter that kept only 36 rows. Add an album(id, library_id) index so library-filtered counts are served by a covering index scan, and make the shared count() helper use count(*) when the rendered query has no join, keeping count(distinct id) wherever a join could fan out rows. id leads the index deliberately: with library_id leading, the planner drives sorted list queries off it and sorts an entire library rather than walking a sort-satisfying index, and fresh statistics do not change that choice. Resolve random pages by id before hydrating them, so the sort pass runs on that covering index with no joins and only the page itself is materialized. Pages larger than 1000 keep the single-query path. The seeded shuffle is unchanged: the id query carries the caller's options, so SEEDEDRAND, explicit seeds, and the reseed-only-at-offset-0 rule all still apply, and the page is reordered in Go to preserve it. Also make the album name sort a total order by appending album.id, and widen album_order_album_name to (order_album_name, order_album_artist_name, id) to serve it. Without a tiebreaker, equal names made page order plan-dependent, which can skip or duplicate rows across paginated requests.