On Wed, Oct 26, 2011 at 5:01 PM, Jay A. Kreibich <j...@kreibi.ch> wrote: > The use of IS is causing the query optimizer to use a full table > scan, essentially turning the query into a O(n) operation. This has > to do with how IS differs from = in the handling of NULLs. Since it > is possible bind a NULL to the query parameter (and one tends to only > use IS when looking for NULLs, so this is a valid assumption), the > optimizer likely assumes the condition in question may return "hit" > for a significant percentage of the table rows. This, and some more > subtle interactions, makes a table scan more appropriate unless you > know the size and diversity of the column in question. Further, the > optimizer cannot replace the IS with a = internally, despite the fact > MyKey is known to be an I.P.K. (other any other column with a NOT > NULL constraint).
Right. You could write SELECT * FROM mytable WHERE MyKey = ? AND MyKey IS NOT NULL; if what you're looking for is non-null keys. If you're looking to include NULL keys then you can do: SELECT * FROM mytable WHERE MyKey IS NULL; Finally, you can always do: SELECT * FROM mytable WHERE :a IS NOT NULL MyKey = :a UNION ALL SELECT * FROM mytable WHERE :a IS NULL AND MyKey IS NULL; This should give you exactly the same results as your original query, but with a fast query plan if there is a suitable index. (It does for me.) The optimizer could re-write your original query as above, but that's probably a fairly hairy optimization to program. That said, it'd be a very useful optimization to program for when you really want IS instead of =... It's nice to be able to write clean SQL and not pay a price in performance for it. But then, SQLite3 is "lite". Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users