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