SQLite IS NULL vs IS NOT NULL query time

Today we were discussing SQLite query performance with respect to “IS NULL”. We are either misremembering, or SQLite has gotten better.

“IS NULL” will leverage an index on a column, just the same as comparing with any other value, such as “== 0”. So, it appears that NULL is indexed. IS NOT NULL, however, is only marginally helped by an index.

Evidence: save attached test.sql and run. Notice how the timing for the query drops from 95ms to 54ms for both “x == 0” as for “x IS NULL” with the addition of an index. The “x IS NOT NULL” query drops only minutely, from 95ms to 85ms.

sqlite3 test2.db < test.sql
SETUP
INSERT 1,000,000 rows, about 50% of which are, randomly NULL, the others are 0
turn on timer
baseline timings with NO INDEX for IS NULL
499969
CPU Time: user 0.095477 sys 0.003140
baseline timings with NO INDEX for IS NOT NULL
500031
CPU Time: user 0.091441 sys 0.003123
baseline timings with NO INDEX for == 0
500031
CPU Time: user 0.094747 sys 0.002805
Create index on x
baseline timings with INDEX for IS NULL
499969
CPU Time: user 0.054242 sys 0.001700
baseline timings with INDEX for IS NOT NULL
500031
CPU Time: user 0.085374 sys 0.002607
baseline timings with INDEX for == 0
500031
CPU Time: user 0.053888 sys 0.001612

SQL:
SELECT “SETUP”;
DROP TABLE IF EXISTS numerals;
CREATE TABLE numerals ( N );

INSERT INTO numerals VALUES ( 0);
INSERT INTO numerals VALUES ( 1 );
INSERT INTO numerals VALUES ( 2 );
INSERT INTO numerals VALUES ( 3 );
INSERT INTO numerals VALUES ( 4 );
INSERT INTO numerals VALUES ( 5 );
INSERT INTO numerals VALUES ( 6 );
INSERT INTO numerals VALUES ( 7 );
INSERT INTO numerals VALUES ( 8 );
INSERT INTO numerals VALUES ( 9 );

DROP TABLE IF EXISTS test;
CREATE TABLE test ( x );

SELECT “INSERT 1,000,000 rows, about 50% of which are, randomly NULL, the others are 0”;

INSERT INTO test select NULLIF(random()>0,1) FROM numerals AS A, numerals AS B, numerals AS C, numerals AS D, numerals AS E, numerals AS F;

SELECT “turn on timer”;

SELECT “baseline timings with NO INDEX for IS NULL”;
.timer on
select COUNT( rowid ) FROM test WHERE x IS NULL;
.timer off
SELECT “baseline timings with NO INDEX for IS NOT NULL”;
.timer on
select COUNT( rowid ) FROM test WHERE x IS NOT NULL;
.timer off
SELECT “baseline timings with NO INDEX for == 0”;
.timer on
select COUNT( rowid ) FROM test WHERE x == 0;
.timer off

SELECT “Create index on x”;
CREATE INDEX test_x ON test (x );

SELECT “baseline timings with INDEX for IS NULL”;
.timer on
select COUNT( rowid ) FROM test WHERE x IS NULL;
.timer off
SELECT “baseline timings with INDEX for IS NOT NULL”;
.timer on
select COUNT( rowid ) FROM test WHERE x IS NOT NULL;
.timer off
SELECT “baseline timings with INDEX for == 0”;
.timer on
select COUNT( rowid ) FROM test WHERE x == 0;
.timer off