Improve Browsing Performance

[mopidy]

Improve Browsing Performance

[tkem]

sqlite> explain query plan SELECT uri, name FROM albums AS album WHERE 'local:artist:md5:e47bef4136ddcf1174824b46725a9a94' IN (SELECT album.artist_uri UNION SELECT artists FROM track WHERE album = album.uri);
0|0|0|SCAN TABLE album
0|1|1|SEARCH TABLE artist USING INDEX sqlite_autoindex_artist_1 (uri=?)
0|0|0|EXECUTE CORRELATED LIST SUBQUERY 1
3|0|0|SEARCH TABLE track USING INDEX track_album_index (album=?)
1|0|0|COMPOUND SUBQUERIES 2 AND 3 USING TEMP B-TREE (UNION)

[tkem]

sqlite> explain query plan SELECT uri, name FROM albums AS album WHERE 'local:artist:md5:e47bef4136ddcf1174824b46725a9a94' IN (SELECT artists FROM track WHERE album = album.uri) OR album.artist_uri = 'local:artist:md5:e47bef4136ddcf1174824b46725a9a94';
0|0|0|SCAN TABLE album
0|1|1|SEARCH TABLE artist USING INDEX sqlite_autoindex_artist_1 (uri=?)
0|0|0|EXECUTE CORRELATED LIST SUBQUERY 1
1|0|0|SEARCH TABLE track USING INDEX track_album_index (album=?)

[tkem]

sqlite> explain query plan SELECT uri, name FROM tracks WHERE album_uri IS NULL AND 'local:artist:md5:e47bef4136ddcf1174824b46725a9a94' IN (artist_uri, albumartist_uri) ORDER BY name;
0|0|0|SCAN TABLE track USING INDEX track_name_index
0|1|1|SEARCH TABLE album USING INDEX sqlite_autoindex_album_1 (uri=?)
0|2|2|SEARCH TABLE artist USING INDEX sqlite_autoindex_artist_1 (uri=?)
0|3|3|SEARCH TABLE artist AS composer USING INDEX sqlite_autoindex_artist_1 (uri=?)
0|4|4|SEARCH TABLE artist AS performer USING INDEX sqlite_autoindex_artist_1 (uri=?)
0|5|5|SEARCH TABLE artist AS albumartist USING INDEX sqlite_autoindex_artist_1 (uri=?)
0|0|0|EXECUTE LIST SUBQUERY 1

[tkem]

sqlite> explain query plan SELECT uri, name FROM track WHERE album IS NULL AND artists = 'local:artist:md5:e47bef4136ddcf1174824b46725a9a94' ORDER BY name;
0|0|0|SEARCH TABLE track USING INDEX track_artists_index (artists=?)
0|0|0|USE TEMP B-TREE FOR ORDER BY

[tkem]

The SCAN TABLE track is probably responsible for this, while the SCAN TABLE album probably accounts for ca. 10% of the total run time (given albums have ca. 10 tracks, each).

[tkem]

sqlite> explain query plan SELECT 'album' AS type, album.uri AS uri, album.name AS name FROM album JOIN track ON album.uri = track.album WHERE ? in (track.artists, album.artists) GROUP BY album UNION SELECT 'track' AS type, uri, name FROM track WHERE artists = ? AND album IS NULL ORDER BY type, name;
1|0|0|SCAN TABLE album (~439 rows)
1|1|1|SEARCH TABLE track USING INDEX track_album_index (album=?) (~6 rows)
1|0|0|EXECUTE LIST SUBQUERY 2
1|0|0|USE TEMP B-TREE FOR GROUP BY
1|0|0|USE TEMP B-TREE FOR ORDER BY
2|0|0|SEARCH TABLE track USING INDEX track_artists_index (artists=?) (~2 rows)
2|0|0|USE TEMP B-TREE FOR ORDER BY
0|0|0|COMPOUND SUBQUERIES 1 AND 2 (UNION)

[tkem]

"The UNION operator works the same way as UNION ALL, except that duplicate rows are removed from the final result set."