Closed tkem closed 9 years ago
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)
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=?)
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
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
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).
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)
"The UNION operator works the same way as UNION ALL, except that duplicate rows are removed from the final result set."
The new browsing scheme introduced in v0.7.0 has some negative effects on performance. Especially on embedded plattforms like the Raspberry Pi, there is a noticable lag when browsing artists or composers. This should be investigated.