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
