Hey all -- I am having a problem with sqlite choosing the wrong index. This is with sqlite 3.5.7, but I believe this has been happening for a while as the general slowness caused in our app due to this has been around for a while. I've posted a reduced test case here:
http://skrul.com/index.sh But basically there is a simple table: create table foo (id integer primary key autoincrement, a integer, b integer); And i fill it with 100,000 rows, with the a column always null and the b column an incrementing integer. I also add two indexes, one on foo.a and one on foo.b, and then run analyze. Now compare the query plans from these two selects: sqlite> explain query plan select * from foo where a is not null order by b; 0|0|TABLE foo WITH INDEX idx_b ORDER BY sqlite> explain query plan select * from foo where a is null order by b; 0|0|TABLE foo WITH INDEX idx_a Note the difference between the two queries is the negation of the where clause. The first query takes about .2s, where the second one takes 1.3s, a pretty significant difference :) I can force the second query to not use idx_a by replacing the where clause with "coalesce(a, 0) = 0" but I don't think this is a great solution. Here is my stat table: sqlite> select * from sqlite_stat1; foo|idx_b|100000 1 foo|idx_a|100000 1 Is there any way to fix this, or do I need to change all my queries to avoid the situation? cheers, -steve _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
