21.09.2014 00:08, Richard Hipp kirjutas: > On Sat, Sep 20, 2014 at 12:45 PM, Merike <gas...@smail.ee> wrote: > >> 19.09.2014 04:21, Richard Hipp kirjutas: >>> A simple script to reproduce the problem in the latest SQLite is as >>> follows: CREATE TABLE t1(a INTEGER PRIMARY KEY, b INTEGER, c INTEGER, >>> d INTEGER); CREATE INDEX t1b ON t1(b); CREATE TABLE t2(x INTEGER >>> PRIMARY KEY, y); explain query plan SELECT * FROM t1, t2 WHERE x=c AND >>> b=?1 ORDER BY a; .print ---------------------- CREATE INDEX t1bd ON >>> t1(b,d); explain query plan SELECT * FROM t1, t2 WHERE x=c AND b=?1 >>> ORDER BY a; If you copy/paste the above script into an sqlite3 >>> command-line shell, you'll see that, for some reason, the query >>> planner decides to sort manually rather than using an index for >>> sorting after the t1bd index is added. We are still investigating to >>> try understand why that is. Please note that if you run ANALYZE on >>> your sample database, the query is fast again. On my (Ubuntu) desktop, >>> the query takes 648 milliseconds before being analyzed and 600 >>> microseconds afterwards - a 1000-fold speedup. >> A question: is the query being fast again after analyze call indicative >> of the bug being fixed? Because I tried it on my original database too >> and there I don't see a speedup after analyze. Should I try to minimize >> it to a smaller database again where the bug still occurs, even after >> analyze? Or will the change you made fix my original database speed as >> well despite the analyze call not helping it? >> > The change fixes the problem (for us) *without* requiring ANALYZE. > Right, that's not what I was trying to ask so I'll try to explain again.
Is there a way to tell if this fix helps the query speed only on the example database I provided or also the original one without waiting for a new release of sqlite? Since you said that analyze helps the example database even without code fix I tried running analyze on original database too, hoping it might speed up that as well. And there it did not help. This might mean nothing, I don't know sqlite well enough to tell, but could it also mean that the fix (when released) might not speed up the same query on my original database? The table structure is the same there but data is different and there's more of it. It could very well be that waiting for the release is the only way to find it out as I'm not interested enough to try to build sqlite myself but I was trying to figure out if there's another way :) Otherwise there could be a chance that next release comes around and might not help the query on actual database and then I would need to post here again and wait for another release. Merike _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
