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. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
