I <think> it's treating "is" as an operator on the value. So with the = version, it knows it can use the index to zip to where the nulls would be and ends pretty quick when it doesn't find any.
With the IS, it thinks it has to check every value because indexes are sorted on =, <, > etc, and not on "is" This is just my guess anyway. What has you checking an integer primary key for null anyway? SQLite version 3.21.0 2017-10-24 18:55:49 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> create table tbl (id integer primary key); sqlite> explain query plan select id from tbl where id = null; selectid|order|from|detail 0|0|0|SEARCH TABLE tbl USING INTEGER PRIMARY KEY (rowid=?) sqlite> explain query plan select id from tbl where id is null; selectid|order|from|detail 0|0|0|SCAN TABLE tbl sqlite> explain select id from tbl where id = null; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 8 0 00 Start at 8 1 OpenRead 0 3 0 0 00 root=3 iDb=0; tbl 2 Explain 0 0 0 SEARCH TABLE tbl USING INTEGER PRIMARY KEY (rowid=?) 00 3 Null 0 1 0 00 r[1]=NULL 4 SeekRowid 0 7 1 00 intkey=r[1]; pk 5 Copy 1 2 0 00 r[2]=r[1] 6 ResultRow 2 1 0 00 output=r[2] 7 Halt 0 0 0 00 8 Transaction 0 0 2 0 01 usesStmtJournal=0 9 Goto 0 1 0 00 Run Time: real 0.000 user 0.000000 sys 0.000000 sqlite> explain select id from tbl where id is null; addr opcode p1 p2 p3 p4 p5 comment ---- ------------- ---- ---- ---- ------------- -- ------------- 0 Init 0 10 0 00 Start at 10 1 OpenRead 0 3 0 0 00 root=3 iDb=0; tbl 2 Explain 0 0 0 SCAN TABLE tbl 00 3 Rewind 0 9 0 00 4 Rowid 0 1 0 00 r[1]=rowid 5 NotNull 1 8 0 00 if r[1]!=NULL goto 8 6 Copy 1 2 0 00 r[2]=r[1] 7 ResultRow 2 1 0 00 output=r[2] 8 Next 0 4 0 01 9 Halt 0 0 0 00 10 Transaction 0 0 2 0 01 usesStmtJournal=0 11 Goto 0 1 0 00 -----Original Message----- From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of x Sent: Friday, January 05, 2018 3:20 PM To: sqlite-users@mailinglists.sqlite.org Subject: [sqlite] difference between 'ID IS NULL' and 'ID = NULL' Tbl has 2.4 million records and has an INTEGER PRIMARY KEY ID which is NOT NULL. SELECT ID FROM Tbl where ID = NULL takes only a few milliseconds to run and the explain query plan shows it uses the primary key. SELECT ID FROM Tbl WHERE ID IS NULL takes around 350 milliseconds to run and the explain query plan shows it uses some covering index. I’m sure I’ve read something before about this but can’t find it in the documentation? _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
