gonic icon indicating copy to clipboard operation
gonic copied to clipboard

Add a covering index on tracks (album_id, length)

Open lomereiter opened this issue 8 months ago • 8 comments

The covering index also includes length, so that SQLite can avoid reading the rows.

In my experiments with the DB provided by @cascooscuro (see https://github.com/sentriz/gonic/issues/478), adding this index already helps to cut the query time from 2.8s to 1.0s. Replacing the join with subqueries cuts down the time further to 0.3s.

Query plan for the original query:

QUERY PLAN
|--SCAN albums
|--SEARCH album_artists USING COVERING INDEX idx_album_artists_album_id (album_id=?)
|--SEARCH tracks USING COVERING INDEX idx_tracks_album_id (album_id=?) LEFT-JOIN
`--USE TEMP B-TREE FOR ORDER BY

When using subqueries:

QUERY PLAN
|--SCAN albums
|--SEARCH album_artists USING COVERING INDEX idx_album_artists_album_id (album_id=?)
|--CORRELATED SCALAR SUBQUERY 1
|  `--SEARCH tracks USING COVERING INDEX idx_tracks_album_id (album_id=?)
|--CORRELATED SCALAR SUBQUERY 2
|  `--SEARCH tracks USING COVERING INDEX idx_tracks_album_id (album_id=?)
`--USE TEMP B-TREE FOR ORDER BY

fixes #478

lomereiter avatar May 25 '24 17:05 lomereiter