I've almost finished debugging an issue in which a certain query was taking
drastically longer for some versions of a database (a music library file)
than for other, similarly sized versions. I have been using SQLite 3.8.2
for this since I don't feel up to try replacing the version that came with
my OS. The basic form of the query (with names shortened) is:
SELECT ...
FROM a,b,c
INNER JOIN d
ON a.tID = d.tID
INNER JOIN e
ON d.eID = e.iID
WHERE
e.mID = 188 AND b.aID = a.aID AND
c.lID = a.lID
AND err = 0
AND (a.lp < 1518483204 OR a.lp IS NULL)
AND (a.ls < 1518483204 OR a.ls IS NULL)
ORDER BY RANDOM () LIMIT 1;
This query was running nearly instantly for some versions of my database,
and took at least 15 seconds for other versions, even if all the tables
involved were the same size. The "slow" query plan (from EXPLAIN QUERY
PLAN) is:
0|0|0|SEARCH TABLE CoreTracks USING AUTOMATIC COVERING INDEX
(LastStreamError=?)
0|1|1|SEARCH TABLE CoreArtists USING INTEGER PRIMARY KEY (rowid=?)
0|2|2|SEARCH TABLE CoreAlbums USING INTEGER PRIMARY KEY (rowid=?)
0|3|4|SEARCH TABLE CoreCache USING AUTOMATIC COVERING INDEX (ModelID=?)
0|4|3|SEARCH TABLE CorePlaylistEntries USING INTEGER PRIMARY KEY (rowid=?)
0|0|0|USE TEMP B-TREE FOR ORDER BY
And the "fast" plan is:
0|0|4|SCAN TABLE CoreCache
0|1|3|SEARCH TABLE CorePlaylistEntries USING INTEGER PRIMARY KEY (rowid=?)
0|2|0|SEARCH TABLE CoreTracks USING INTEGER PRIMARY KEY (rowid=?)
0|3|1|SEARCH TABLE CoreArtists USING INTEGER PRIMARY KEY (rowid=?)
0|4|2|SEARCH TABLE CoreAlbums USING INTEGER PRIMARY KEY (rowid=?)
0|0|0|USE TEMP B-TREE FOR ORDER BY
So my question became why SQLite was sometimes using the (much) slower
query plan. I found that a reliable way to get it to use the faster query
plan was to create a new index on d(eID, pID)--the same as an
already-existing index, but with the two indexed columns reversed. This
causes SQLite to use the faster query plan where it previously used the
slower one. But I'm not sure why this is the case, given that the faster
query plan uses no indexes at all.
So I'm wondering not just for this query, but in general, how can adding an
index cause a query plan to change even if the new query plan doesn't make
use of the new index?
-David Pitchford
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
