A little hard to debug without the actual schema. You're giving us the query with a, b, c etc but the query plan with the real names.
The slow plan is using an automatic index on CoreCache (ModelID), ie it's making a new index when it runs and then dumping the index at the end of the query. That's probably the slowest part and it's pushing it off as long as it can when it can use other indexes to potentially filter things out first. Is that the field d(eID) that you mention you're creating an index on? "the same as an already-existing index, but with the two indexed columns reversed" Remember that the usefulness of an index depends on the ordering of the fields. An index on (b, a) isn't useful if you're looking for a, it's only useful if you're looking for b. Consider ye olde phone book. It's an index on lastname, firstname. If I ask you to go through the New York City phonebook and find everyone with the lastname Pitchford you flip to the P's and life is good. If I ask you to find everyone with the firstname David then it doesn't matter that firstname is the second field of the index, you still have to flip through the entire thing to find all the David's. -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of David Pitchford Sent: Thursday, February 15, 2018 6:32 PM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] How does adding an index change a query plan even though the new query plan doesn't use the newly added index? 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 _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
