Does this make sense to try?
First, duplicate the lack of index....
sqlite> explain query plan select * from x where col1 is null;
sele order from deta
---- ------------- ---- ----
0 0 0 SEARCH TABLE x USING INDEX col1index (col1=?) (~10
rows)
sqlite> explain query plan select * from x where col1 is not null;
sele order from deta
---- ------------- ---- ----
0 0 0 SCAN TABLE x (~500000 rows)
What does distinct do?
sqlite> explain query plan select distinct (col1) from x where col1 is not null;
sele order from deta
---- ------------- ---- ----
0 0 0 SCAN TABLE x USING COVERING INDEX col1index (~500000
rows)
OK...we're still using an index here...so using this subselect we do this:
sqlite> explain query plan select * from x where col1 in (select distinct
(col1) from x where col1 is not null);
sele order from deta
---- ------------- ---- ----
0 0 0 SEARCH TABLE x USING INDEX col1index (col1=?) (~250
rows)
0 0 0 EXECUTE LIST SUBQUERY 1
1 0 0 SCAN TABLE x USING COVERING INDEX col1index (~500000
rows)
Performance probably depends on how many "not null" things there are...if not
many of them this may not be any faster.
A count() could be a lot faster though I'd think.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Igor Tandetnik [itandet...@mvps.org]
Sent: Monday, October 17, 2011 6:56 AM
To: sqlite-users@sqlite.org
Subject: EXT :Re: [sqlite] "is not null" and index
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.
--
Igor Tandetnik
_______________________________________________
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
