clementine-player/Clementine — pull request #4285 by paperbagcorner (2014-04-19 12:27:58+00:00)
Title: Fix slow library search on sqlite 3.8.

Library search in Clementine is very slow when using sqlite 3.8. This has been reported in issues #3847, #4265 and possibly others.

The problem lies in queries of the form
````
SELECT DISTINCT artist FROM songs INNER JOIN songs_fts AS fts ON songs.ROWID = fts.ROWID WHERE fts.songs_fts MATCH "w*" AND effective_compilation = 0 AND unavailable = 0;
````
Running
````
EXPLAIN QUERY PLAN SELECT DISTINCT artist FROM songs INNER JOIN test_songs_fts AS fts ON songs.ROWID = fts.ROWID WHERE fts.test_songs_fts MATCH "w*" AND effective_compilation = 0;
````

gives the following result on sqlite 3.7:
````
0|0|1|SCAN TABLE test_songs_fts AS fts VIRTUAL TABLE INDEX 11: (~0 rows)
0|1|0|SEARCH TABLE songs USING INTEGER PRIMARY KEY (rowid=?) (~1 rows)
0|0|0|USE TEMP B-TREE FOR DISTINCT
````
whereas on sqlite 3.8, the result becomes
````
0|0|0|SEARCH TABLE songs USING COVERING INDEX idx_comp_artist (effective_compilation=?)
0|1|1|SCAN TABLE test_songs_fts AS fts VIRTUAL TABLE INDEX 11:

````
As you can see, sqlite 3.8 uses the index idx_comp_artist and thereby nests the tables in opposite order.

This pull request modifies the query by explicitly telling sqlite not to use this index. The result of EXPLAIN QUERY on sqlite 3.8 with this patch applied is
````
0|0|1|SCAN TABLE test_songs_fts AS fts VIRTUAL TABLE INDEX 11:
0|1|0|SEARCH TABLE songs USING INTEGER PRIMARY KEY (rowid=?)
````
i.e. the tables are nested in the same order as in sqlite 3.7 and the library search is fast once again.

It should also be noted that as an alternative to this patch, it is sufficient to run ANALYZE on the songs table in sqlite 3.8.