sqlite> explain query plan select * from foo where a is not null; order|from|detail 0|0|TABLE foo
sqlite> explain query plan select * from foo where a is null; order|from|detail 0|0|TABLE foo WITH INDEX idx_a Sqlite is using the fact that You have an order by clause on both statements. Removing the order by clears this up.... the first query sqlite is not able to use and index and must full scan the table. It then uses the fact that an order by is used on column B. Thus it accesses idx_b! (which is correct). the second query sqlite decides to use the index on idx_A and thats how it performs its access. The order by is then done internally on the resulting data! HTH, Ken Steve Krulewitz <[EMAIL PROTECTED]> wrote: 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 Steve Krulewitz <[EMAIL PROTECTED]> wrote: 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 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
