gonic
gonic copied to clipboard
Add a covering index on tracks (album_id, length)
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