Hi, I experienced a serious performance regression with a rather simple query. The situation can be reproduced as:
SQLite version 3.8.3.1 2014-02-11 14:52:19 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> CREATE TABLE t (a INTEGER, b INTEGER, c INTEGER, d BLOB); sqlite> CREATE INDEX appropriate ON t (a, b, c); sqlite> CREATE INDEX inappropriate ON t (b, c); sqlite> EXPLAIN QUERY PLAN SELECT d FROM t WHERE a = ? AND b = ? AND c >= ? ORDER BY c; selectid order from detail ---------- ---------- ---------- ------------------------------------------------------ 0 0 0 SEARCH TABLE t USING INDEX inappropriate (b=? AND c>?) That is, a clearly inferior index is chosen. Adding an INDEXED BY clause to the query to force the right index yields a 50x speedup in my case (compared to 3.7.16.2). Thanks for investigating, Gergely Peli _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
