[sqlite] Wrong Index Select with Large Table Joined to Small Table

[Richard Hipp]

On 1/21/16, Denis Burke <burkeden at gmail.com> wrote:
> When
> would you NOT want to add the "where [indexedColumn] IS NOT NULL"?  Seems
> like it would always be helpful.
>

CREATE TABLE t1(x);
CREATE INDEX t1x ON t1(x) WHERE x IS NOT NULL;

That index will not work on queries like this (obviously):

     SELECT * FROM t1 WHERE x IS NULL;

The index is also useless for sorting:

     SELECT * FROM t1 ORDER BY x;

Rule of thumb: Only use "WHERE x IS NOT NULL" if the column being
indexed contains many, many NULL values.

-- 
D. Richard Hipp
drh at sqlite.org