quintodrome/db/migrations/20260520211813_add_media_file_artists_composite_index.sql

-- +goose Up
CREATE INDEX IF NOT EXISTS media_file_artists_media_file_id_role
    ON media_file_artists (media_file_id, role);
DROP INDEX IF EXISTS media_file_artists_media_file_id;

-- +goose Down
CREATE INDEX IF NOT EXISTS media_file_artists_media_file_id
    ON media_file_artists (media_file_id);
DROP INDEX IF EXISTS media_file_artists_media_file_id_role;
fix(smartplaylists): optimize smart playlist performance for role and tag criteria (#5515) * fix(server): optimize smart playlist role queries for large criteria (#5511) Role-based smart playlist criteria (artist, composer, etc.) now query the indexed media_file_artists join table instead of parsing JSON via json_tree() on every row. Multiple conditions for the same role within an OR group are merged into a single EXISTS subquery (batched at 200 to stay under SQLite's expression tree depth limit). A composite index (media_file_id, role) replaces the now-redundant single-column (media_file_id) index on media_file_artists. Benchmark (40k tracks, 500 patterns, 3 artists/track): - Merged join-table: 15ms (9.3x faster) - Merged json_tree: 30ms (4.6x faster) - Unmerged baseline: 137ms * refactor: simplify role condition SQL generation and benchmark Extract shared roleCondSQL/roleExistsSQL helpers to deduplicate the EXISTS template between roleCond and roleCondGroup. Use slices.Chunk for batching per project convention. Extract runBenchQuery helper to eliminate triplicated benchmark execution loop. * chore: raise roleCondBatchSize to 350 The empirical SQLite limit is 496 conditions per merged EXISTS subquery. Raising from 200 to 350 reduces the number of batches (e.g. 500 patterns now splits into 2 batches instead of 3). * fix(server): apply OR-merge optimization to tag conditions too Generalize mergeRoleConds into mergeJsonConds to also collapse multiple tag conditions for the same tag (e.g. genre) within OR groups. This gives the same ~5x speedup for tag-heavy smart playlists as the role optimization gives for artist-heavy ones. * refactor: benchmark uses real criteria pipeline instead of hand-built SQL The "Current" sub-benchmark now builds criteria.Criteria expressions and runs them through the actual newSmartPlaylistCriteria → Where() → ToSql() pipeline, validating the real production code path. The baseline still uses hand-built SQL representing the old json_tree approach. * fix: stabilize merged group ordering and close rows before error check Sort group keys in mergeJsonConds so the merged additions have deterministic order across runs, improving SQLite statement cache reuse. Move rows.Close() before rows.Err() in benchmark helper. 2026-05-22 13:00:13 -08:00			`-- +goose Up`
			`CREATE INDEX IF NOT EXISTS media_file_artists_media_file_id_role`
			`ON media_file_artists (media_file_id, role);`
			`DROP INDEX IF EXISTS media_file_artists_media_file_id;`

			`-- +goose Down`
			`CREATE INDEX IF NOT EXISTS media_file_artists_media_file_id`
			`ON media_file_artists (media_file_id);`
			`DROP INDEX IF EXISTS media_file_artists_media_file_id_role;`