On Apr 10, 2008, at 1:30 PM, Steve Krulewitz wrote: > > This problem also affects the table ordering of my joins. Taking the > same schema and adding a "detail" table (see > http://skrul.com/index_detail.sh) , this query chooses idx_a and puts > the foo table first: > > sqlite> explain query plan select * from foo join foo_detail on foo.id > = foo_detail.id where (a is null) and data = 1; > 0|0|TABLE foo WITH INDEX idx_a > 1|1|TABLE foo_detail WITH INDEX idx_foo_detail_data > > And it runs very very _very_ slowly. Forcing sqlite not to use > idx_a, you get: > > sqlite> explain query plan select * from foo join foo_detail on foo.id > = foo_detail.id where (a is null or a is not null) and data = 1; > 0|1|TABLE foo_detail WITH INDEX idx_foo_detail_data > 1|0|TABLE foo USING PRIMARY KEY > > And it runs brilliantly fast as you'd expect. > > For now I've added the "or" hack into my application (thanks, Ken) and > this has sped things up considerably. Is there any better way to fix > this? >
Use "+a is null" instead of "a is null". (Add a "+" in front of the column name). The plus sign is a no-op - it generates no code and returns the value of its operand unchanged even if the value is a string. But it also disables the term as a candidate for using indices. D. Richard Hipp [EMAIL PROTECTED] _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
