Yoav Apter <yo...@checkpoint.com> wrote:
> I have the following table:
>
> CREATE TABLE x ('col1', 'col2', 'col3')
> Create col1index on x ('col1')
>
> When I run this query: "select * from x where col1 is null" I see
the index on x is used.
> When I run this query: "select * from x where col1 is NOT null" I
see the index on x is not used.
Why is that suprising? Imagine you are given a book with an index at
the end, and are asked to enumerate all pages where a particular term
does *not* appear. Would an index be helpful in this task?
> How can indexes be used with "not null" queries?
They cannot.
If one sees NOT NULL as the complement of NULL, i.e. values in the
range {min_value, max_value} (min and max depending on the column
expected content and type), then couldn't an index help?
select * from table where col between min_value and max_value
Of course if there are only few NULLs then a table scan will probably
be faster.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
