Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
Thanks Ryan and Dominique. The quote “You can think of an SQLite table as essentially a btree covering Index by itself with the Key being the Row_ID” makes things a lot clearer. Thanks to everyone for their replies and patience. Regards Tom From: sqlite-users on behalf of Dominique Devienne Sent: Monday, January 8, 2018 10:56:55 AM To: SQLite mailing list Subject: Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL' On Mon, Jan 8, 2018 at 11:39 AM, x wrote: > However, I’m still confused. Reading this https://sqlite.org/ > queryplanner.html suggests the table is stored in RowID order. So what > happens if I insert a record into Tbl with a lower ID than the existing 2.4 > million Ids? > It depends if your ID column is an alias for the ROWID special column or not. ROWID is the key of the B-tree I believe, so if you insert a row "in the middle", many pages have potentially to be rewritten to "rebalance" the B-tree. While only the "last" page needs updating with AUTO INCREMENT or a ROWID larger than the previous larger one. So if ID an alias for ROWID, the insert might be much more expensive. If ID is not, then the row is simply "appended" at the end, "cheaply". I could be wrong of course. I'll find out very soon :). --DD ___ 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
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
On Mon, Jan 8, 2018 at 12:33 PM, R Smith wrote: > using any other index means a round-trip reading and hitting values in > THAT index, then returning and looking up the hit result in the rowid table index, and then reading the pages(s) from it and extracting the data - where during a table scan, all this round tripping is skipped. > Plus during a table-scan, you're reading the table pages "in-order", and decoding the page only once, for all rows on each page. While an index-scan read the index "in-order", but the table pages "out-of-order", since most consecutive rows (from the index) end up on different table pages. The page-cache helps to avoid too much IO (if big enough), but you must still decode the page for each row to access one specific record inside it. Sometimes the full-table-scan is the most efficient indeed. --DD PS: But also note that a query with an order-by or group-by might still prefer an index-scan to avoid sorting rows, if the index already matches the requested order, offsetting the eventual higher cost from the index-scan. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
On 2018/01/08 12:39 PM, x wrote: Thanks Cezary and Scott. I’m now a bit clearer as to what’s happening. I imagined the RowID as being a separate index which is the root of my confusion. To elaborate a little - We often get people here asking "But why it table-scans in stead of using my nice Index?". This stems from an often-held misconception that Indexes are God-sent magic to improve everything. The fact is that Indexes are costly mechanisms which allows fast lookup which, only AFTER a certain critical size and for specific circumstances, become more efficient than a scan. The Query Planner has to do a lot of work to figure out what those "critical size and specific circumstances" is for any specific query, and it does get real fuzzy. I think I've heard Richard or Dan explain it as follows (if memory serves, someone please point out if I'm mistaken): You can think of an SQLite table as essentially a btree covering Index by itself with the Key being the Row_ID (or more recently, the PK for WITHOUT ROWID tables). This is why the rowid (or any column serving as an alias to it, or the PK for WITHOUT ROWID tables) cannot have NULL values, but any other primary key could (in SQLite). Being an Index by itself means that a Table-Scan is perhaps not as inefficient as one might think and indeed using any other index means a round-trip reading and hitting values in THAT index, then returning and looking up the hit result in the rowid table index, and then reading the pages(s) from it and extracting the data - where during a table scan, all this round tripping is skipped. So unless any prospective candidate Index for any query offers a truly magnificent cost advantage, a table scan would probably be more efficient, and so be chosen. This is why running ANALYZE on large tables is needed, because it allows the QP to better deduce whether a prospective Index might in fact offer such a magnificent cost reduction or not. Another way is hinting at the QP (Search "likelihood" in the docs). This is why a non-rowid-alias Primary Key on a rowid table is also less efficient to scan than the table itself (often very non-intuitive) - or - why a covering index sometimes gets avoided in a JOIN when it seems to contain all needed data to fulfill the join obligation. Also, often a great index is not used simply because the query planner does not know enough about it and its prospective cost to obtain a good estimate of its utility, and sometimes what feels intuitively to us as a great Index just isn't really. The QP is not infallible, but it is quite smart. Cheers, Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
[Repost: used a wrong e-mail at first, apologies if this comes through twice] On 2018/01/08 12:39 PM, x wrote: Thanks Cezary and Scott. I’m now a bit clearer as to what’s happening. I imagined the RowID as being a separate index which is the root of my confusion. It would explain this To elaborate a little - We often get people here asking "But why it table-scans in stead of using my nice Index?". This stems from an often-held misconception that Indexes are God-sent magic to improve everything. The fact is that Indexes are costly mechanisms which allows fast lookup which, only AFTER a certain critical size and for specific circumstances, become more efficient than a scan. The Query Planner has to do a lot of work to figure out what those "critical size and specific circumstances" is for any specific query, and it does get real fuzzy. I think I've heard Richard or Dan explain it as follows (if memory serves, someone please point out if I'm mistaken): You can think of an SQLite table as essentially a btree covering Index by itself with the Key being the Row_ID (or more recently, the PK for WITHOUT ROWID tables). This is why the rowid (or any column serving as an alias to it, or the PK for WITHOUT ROWID tables) cannot have NULL values, but any other primary key could (in SQLite). Being an Index by itself means that a Table-Scan is perhaps not as inefficient as one might think and indeed using any other index means a round-trip reading and hitting values in THAT index, then returning and looking up the hit result in the rowid table index, and then reading the pages(s) from it and extracting the data - where during a table scan, all this round tripping is skipped. So unless any prospective candidate Index for any query offers a truly magnificent cost advantage, a table scan would probably be more efficient, and so be chosen. This is why running ANALYZE on large tables is needed, because it allows the QP to better deduce whether a prospective Index might in fact offer such a magnificent cost reduction or not. Another way is hinting at the QP (Search "likelihood" in the docs). This is why a non-rowid-alias Primary Key on a rowid table is also less efficient to scan than the table itself (often very non-intuitive) - or - why a covering index sometimes gets avoided in a JOIN when it seems to contain all needed data to fulfill the join obligation. Also, often a great index is not used simply because the query planner does not know enough about it and its prospective cost to obtain a good estimate of its utility, and sometimes what feels intuitively to us as a great Index just isn't really. The QP is not infallible, but it is quite smart. Cheers, Ryan ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
On Mon, Jan 8, 2018 at 11:39 AM, x wrote: > However, I’m still confused. Reading this https://sqlite.org/ > queryplanner.html suggests the table is stored in RowID order. So what > happens if I insert a record into Tbl with a lower ID than the existing 2.4 > million Ids? > It depends if your ID column is an alias for the ROWID special column or not. ROWID is the key of the B-tree I believe, so if you insert a row "in the middle", many pages have potentially to be rewritten to "rebalance" the B-tree. While only the "last" page needs updating with AUTO INCREMENT or a ROWID larger than the previous larger one. So if ID an alias for ROWID, the insert might be much more expensive. If ID is not, then the row is simply "appended" at the end, "cheaply". I could be wrong of course. I'll find out very soon :). --DD ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
Thanks Cezary and Scott. I’m now a bit clearer as to what’s happening. I imagined the RowID as being a separate index which is the root of my confusion. It would explain this sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL ORDER BY ID; 0|0|0|SCAN TABLE TBL contrasted with sqlite> EXPLAIN QUERY PLAN SELECT X FROM TBL ORDER BY X; 0|0|0|SCAN TABLE TBL USING COVERING INDEX XXX However, I’m still confused. Reading this https://sqlite.org/queryplanner.html suggests the table is stored in RowID order. So what happens if I insert a record into Tbl with a lower ID than the existing 2.4 million Ids? From: sqlite-users on behalf of Scott Robison Sent: Sunday, January 7, 2018 7:30:12 PM To: SQLite mailing list Subject: Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL' Integer primary key is by definition not null, so looking for a null value on an index can't work. I guess there exists an optimization opportunity to just return an emotional set, though it seems easier to not specify an impossible condition. As to why it does a table scan, the primary key isn't a separate index. The rowid primary key is part of the table itself. On Jan 7, 2018 11:22 AM, "Luuk" wrote: > On 07-01-18 19:09, x wrote: > >> Because reading the whole record (all 3 fields) is more expensive than > >> just reading the index which has all the info you need to give a correct > >> answer on 'SELECT ID FROM TBL WHERE ID IS NULL;' > > Yes, but the covering index has 2 fields (X & ID). The pk has only 1 > field (ID) so why not use that, particularly in the case where ‘ORDER BY > ID’ was included in the query? > > > > > sorry, i do not know > ___ > 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 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
Hello, To summarize: On 2018-01-07 19:09, x wrote: Because reading the whole record (all 3 fields) is more expensive than just reading the index which has all the info you need to give a correct answer on 'SELECT ID FROM TBL WHERE ID IS NULL;' Yes, but the covering index has 2 fields (X & ID). The pk has only 1 field (ID) so why not use that, particularly in the case where ‘ORDER BY ID’ was included in the query? OK - your creation statement causes that ``ID'' is an alias for ``rowid''. Why PK is not used, was mentioned by me: On 2018-01-06 14:01, Cezary H. Noweta wrote: Because the field is not NULL in Luuk's definition and NULL values are not covered by the INDEX. SQLite assumes that you know what you are doing and tries to find NULL values by full scan. and Scott: On 2018-01-07 20:30, Scott Robison wrote: Integer primary key is by definition not null, so looking for a null value on an index can't work. Look at ``where.c:whereLoopAddBtreeIndex()'': if( (eOp==WO_ISNULL || (pTerm->wtFlags&TERM_VNULL)!=0) && indexColumnNotNull(pProbe, saved_nEq) ){ continue; /* ignore IS [NOT] NULL constraints on NOT NULL columns */ } Even if you used ``ID INT PRIMARY...'' instead of ``ID INTEGER PRIMARY...'' (which did not aliased ``rowid'' to ``ID'') results would be the same, because ``ID'' is not NULL. Now we are considering a full scan. As Luuk wrote: On 2018-01-07 18:55, Luuk wrote: Because reading the whole record (all 3 fields) is more expensive than just reading the index which has all the info you need to give a correct answer on 'SELECT ID FROM TBL WHERE ID IS NULL;' Calculating a cost of a DB's searching is a bit complicated and cannot be described in two words. You could start at a comment of ``where.c:whereLoopAddBtree()''. When your ratio of a length of an index element to a length of a table row has been lowered, then index ``XXX'' is chosen as the most effective solution. (``rowid'' pseudo-index' entries are considered as being equal to a whole row of a table). However this index is used for a full scan -- not for a binary/log_2n searching for values: ``Yahoo! My query is using an index so it achieved a speed of light''. If it was true, then certainly ``XXX'' would be used earlier. If you want to enforce some index to be used you will have to append ``INDEXED BY'' clause, however: 1. Choosing a bad index will result in no index at all. 2. As Scott noticed: On 2018-01-07 20:30, Scott Robison wrote: As to why it does a table scan, the primary key isn't a separate index. The rowid primary key is part of the table itself. For the purpose of ``SELECT'' there is a pseudo-index created for ``rowid'' -- it is impossible to enforce that pseudo-index. Unless you use ``INT'' instead of ``INTEGER'' in your ``CREATE TABLE'' statement (which will leave ``rowid'', and ``ID'' will not be alias for ``rowid'') -- such primary index will be named ,,sqlite_autoindex__'' -- you will have to guess (or look into a db-file, or -- at best -- SELECT from ``sqlite_master''). Primary key ``rowid'' (or alias) is not indexed, only ``primary keys'' other then ``rowid'' are. In fact it is impossible to have NULL ``rowid'' (or alias) in destroyed db -- due to a fact that it has NOT NULL, int64 (variable length) format -- there is no place for ``I'm a NULL'' flag. Non-rowid, NOT NULL, primary keys with NULL value could be occurring eventually. I hope, above will help and dispel any doubts. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
Integer primary key is by definition not null, so looking for a null value on an index can't work. I guess there exists an optimization opportunity to just return an emotional set, though it seems easier to not specify an impossible condition. As to why it does a table scan, the primary key isn't a separate index. The rowid primary key is part of the table itself. On Jan 7, 2018 11:22 AM, "Luuk" wrote: > On 07-01-18 19:09, x wrote: > >> Because reading the whole record (all 3 fields) is more expensive than > >> just reading the index which has all the info you need to give a correct > >> answer on 'SELECT ID FROM TBL WHERE ID IS NULL;' > > Yes, but the covering index has 2 fields (X & ID). The pk has only 1 > field (ID) so why not use that, particularly in the case where ‘ORDER BY > ID’ was included in the query? > > > > > sorry, i do not know > ___ > 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
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
On 07-01-18 19:09, x wrote: >> Because reading the whole record (all 3 fields) is more expensive than >> just reading the index which has all the info you need to give a correct >> answer on 'SELECT ID FROM TBL WHERE ID IS NULL;' > Yes, but the covering index has 2 fields (X & ID). The pk has only 1 field > (ID) so why not use that, particularly in the case where ‘ORDER BY ID’ was > included in the query? > > sorry, i do not know ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
>Because reading the whole record (all 3 fields) is more expensive than >just reading the index which has all the info you need to give a correct >answer on 'SELECT ID FROM TBL WHERE ID IS NULL;' Yes, but the covering index has 2 fields (X & ID). The pk has only 1 field (ID) so why not use that, particularly in the case where ‘ORDER BY ID’ was included in the query? From: Luuk<mailto:luu...@gmail.com> Sent: 07 January 2018 18:05 To: sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org> Subject: Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL' On 07-01-18 18:49, x wrote: > Luuk and Cezary, my apologies. Looking at my opening post(s) I didn’t make it > clear that Tbl had numerous secondary indexes attached to it. The table has > 2.4 million records and 13 columns. There is a non-unique index on one of the > other columns (x integer) which is the one appearing in my explain query > plan. The following illustrates the problem. I’ve no idea why adding the 3rd > column is necessary to replicate it. > Because reading the whole record (all 3 fields) is more expensive than just reading the index which has all the info you need to give a correct answer on 'SELECT ID FROM TBL WHERE ID IS NULL;' > > sqlite> CREATE TABLE TBL (ID INTEGER PRIMARY KEY NOT NULL); > > sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL; > > 0|0|0|SCAN TABLE TBL > > sqlite> ALTER TABLE TBL ADD COLUMN X INTEGER; > > sqlite> CREATE INDEX XXX ON TBL(X); > > sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL; > > 0|0|0|SCAN TABLE TBL > > sqlite> ALTER TABLE TBL ADD COLUMN Y INTEGER; > > sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL; > > 0|0|0|SCAN TABLE TBL USING COVERING INDEX XXX > > sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE 0; > > 0|0|0|SCAN TABLE TBL USING COVERING INDEX XXX > > sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL ORDER BY ID; > > 0|0|0|SCAN TABLE TBL USING COVERING INDEX XXX > > sqlite> > > ___ 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
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
On 07-01-18 18:49, x wrote: > Luuk and Cezary, my apologies. Looking at my opening post(s) I didn’t make it > clear that Tbl had numerous secondary indexes attached to it. The table has > 2.4 million records and 13 columns. There is a non-unique index on one of the > other columns (x integer) which is the one appearing in my explain query > plan. The following illustrates the problem. I’ve no idea why adding the 3rd > column is necessary to replicate it. > Because reading the whole record (all 3 fields) is more expensive than just reading the index which has all the info you need to give a correct answer on 'SELECT ID FROM TBL WHERE ID IS NULL;' > > sqlite> CREATE TABLE TBL (ID INTEGER PRIMARY KEY NOT NULL); > > sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL; > > 0|0|0|SCAN TABLE TBL > > sqlite> ALTER TABLE TBL ADD COLUMN X INTEGER; > > sqlite> CREATE INDEX XXX ON TBL(X); > > sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL; > > 0|0|0|SCAN TABLE TBL > > sqlite> ALTER TABLE TBL ADD COLUMN Y INTEGER; > > sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL; > > 0|0|0|SCAN TABLE TBL USING COVERING INDEX XXX > > sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE 0; > > 0|0|0|SCAN TABLE TBL USING COVERING INDEX XXX > > sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL ORDER BY ID; > > 0|0|0|SCAN TABLE TBL USING COVERING INDEX XXX > > sqlite> > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
Luuk and Cezary, my apologies. Looking at my opening post(s) I didn’t make it clear that Tbl had numerous secondary indexes attached to it. The table has 2.4 million records and 13 columns. There is a non-unique index on one of the other columns (x integer) which is the one appearing in my explain query plan. The following illustrates the problem. I’ve no idea why adding the 3rd column is necessary to replicate it. sqlite> CREATE TABLE TBL (ID INTEGER PRIMARY KEY NOT NULL); sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL; 0|0|0|SCAN TABLE TBL sqlite> ALTER TABLE TBL ADD COLUMN X INTEGER; sqlite> CREATE INDEX XXX ON TBL(X); sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL; 0|0|0|SCAN TABLE TBL sqlite> ALTER TABLE TBL ADD COLUMN Y INTEGER; sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL; 0|0|0|SCAN TABLE TBL USING COVERING INDEX XXX sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE 0; 0|0|0|SCAN TABLE TBL USING COVERING INDEX XXX sqlite> EXPLAIN QUERY PLAN SELECT ID FROM TBL WHERE ID IS NULL ORDER BY ID; 0|0|0|SCAN TABLE TBL USING COVERING INDEX XXX sqlite> From: sqlite-users on behalf of Luuk Sent: Sunday, January 7, 2018 1:35:43 PM To: sqlite-users@mailinglists.sqlite.org Subject: Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL' On 06-01-18 19:00, x wrote: >>> sqlite> Explain query plan select ID from Tbl where ID is null order by ID; >>> selectid|order|from|detail >>> 0|0|0|SCAN TABLE Tbl >> I have the same results. ``SCAN TABLE Tbl USING COVERING INDEX ...'' is >> returned in case of not ``NOT NULL'' field. > > > Yeah, I would get that result as well if I had no secondary indexes on Tbl. > If either you or Luuk add a secondary index XXX to your versions of Tbl > you’ll get the same result I’m getting and maybe then we’ll be on the same > page regarding ordering. > > > I still do not knwo what you are doing... multiple indexes define, and i stillsee 'SCAN TABLE ...' can you post the output of your table definition? sqlite> .schema tbl CREATE TABLE tbl (id integer primary key not null); CREATE INDEX idx2 on tbl(id); CREATE INDEX idx3 on tbl(id DESC); sqlite> explain query plan select id from tbl where id is null; 0|0|0|SCAN TABLE tbl sqlite> ___ 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
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
On 06-01-18 19:00, x wrote: >>> sqlite> Explain query plan select ID from Tbl where ID is null order by ID; >>> selectid|order|from|detail >>> 0|0|0|SCAN TABLE Tbl >> I have the same results. ``SCAN TABLE Tbl USING COVERING INDEX ...'' is >> returned in case of not ``NOT NULL'' field. > > > Yeah, I would get that result as well if I had no secondary indexes on Tbl. > If either you or Luuk add a secondary index XXX to your versions of Tbl > you’ll get the same result I’m getting and maybe then we’ll be on the same > page regarding ordering. > > > I still do not knwo what you are doing... multiple indexes define, and i stillsee 'SCAN TABLE ...' can you post the output of your table definition? sqlite> .schema tbl CREATE TABLE tbl (id integer primary key not null); CREATE INDEX idx2 on tbl(id); CREATE INDEX idx3 on tbl(id DESC); sqlite> explain query plan select id from tbl where id is null; 0|0|0|SCAN TABLE tbl sqlite> ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
>>sqlite> Explain query plan select ID from Tbl where ID is null order by ID; >>selectid|order|from|detail >>0|0|0|SCAN TABLE Tbl >I have the same results. ``SCAN TABLE Tbl USING COVERING INDEX ...'' is >returned in case of not ``NOT NULL'' field. Yeah, I would get that result as well if I had no secondary indexes on Tbl. If either you or Luuk add a secondary index XXX to your versions of Tbl you’ll get the same result I’m getting and maybe then we’ll be on the same page regarding ordering. Regards Tom From: sqlite-users on behalf of Cezary H. Noweta Sent: Saturday, January 6, 2018 3:09:59 PM To: SQLite mailing list Subject: Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL' Hello, On 2018-01-06 15:22, x wrote: >> Because the field is not NULL in Luuk's definition and NULL values are >> not covered by the INDEX. SQLite assumes that you know what you are >> doing and tries to find NULL values by full scan. > > > > The ID field in my definition is also not null. If so, then you should obtain results mentioned by Luuk: sqlite> Explain query plan select ID from Tbl where ID is null order by ID; selectid|order|from|detail 0|0|0|SCAN TABLE Tbl I have the same results. ``SCAN TABLE Tbl USING COVERING INDEX ...'' is returned in case of not ``NOT NULL'' field. >> ``SCAN TABLE Tbl USING COVERING INDEX ...'' means that your index is >> used. Order by does not apply as it is the same as PK. Scanning by using >> PK results in ORDERed BY PK records. There is no need to use separate >> INDEX for ORDER BY clause. > > > > I’m not sure what you’re saying there Cezary. I recognise that the > (non-existent) result set will all be NULLs and the order is therefore > meaningless but if the query planner recognises that fact why does it not > recognise that there will be no result set. Order is meaningful but it is the same as order of a table scanning process -- this is why ORDER BY is ``ignored'' -- not because there will be an empty result set. If you change to ORDER BY ID*2, then you will see that temporary index will be created. The same index is used for scanning and ordering, so there is no need to use it twice. > If I run explain query plan select ID from Tbl where 0; // that’s where zero > > > > I again get the result > > > > SCAN TABLE Tbl USING COVERING INDEX XXX Again, I have the sole ``SCAN TABLE'' (without index -- in both cases: NULLs allowed and NOT NULL). Your result is obtained as if it was ORDER BY clause. > However the EXPLAIN for the same query returns > > > > addropcode p1p2p3 p4p5comment > > 0 Init 0 8 0 00 Start at 8 > > 1 Goto 0 7 0 00 > > 2 OpenRead 1 109 0 k(2,,) 00root=109 iDb=0; tID > > 3 Rewind 1 7 1 0 00 > > 4 IdxRowid1 1 0 00r[1]=rowid > > 5 ResultRow 1 1 0 00output=r[1] > > 6 Next1 4 0 01 > > 7 Halt 0 0 0 00 > > 8 Transaction0 0 392 0 01usesStmtJournal=0 > > 9 Goto 0 1 0 00 > > > > which, if I’m reading it correctly, executes 0, 8, 9, 1 & 7 (Halt) and so > comes up with the empty result set in a few milliseconds. That contrasts with > the EXPLAINs of the IS NULL queries mentioned earlier which do an actual > table scan. Indeed -- I have nearly the same: SQLite jumps directly to Halt in case of WHERE 0, except that I have Column instead of IdxRowid. Could you provide your table's CREATE command? AFAIR you are using SQLite 3.21, are not you? -- best regards Cezary H. Noweta ___ 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
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
>Neither will return any values since a primary key can not be NULL. Untrue. The RowID alias "integer primary key" cannot be NULL However components of primary key(...) can be null (ie, that are not aliases for the RowID) and are not the PRIMARY KEY of a without rowid table. " According to the SQL standard, PRIMARY KEY should always imply NOT NULL. Unfortunately, due to a bug in some early versions, this is not the case in SQLite. Unless the column is an INTEGER PRIMARY KEY or the table is a WITHOUT ROWID table or the column is declared NOT NULL, SQLite allows NULL values in a PRIMARY KEY column. SQLite could be fixed to conform to the standard, but doing so might break legacy applications. Hence, it has been decided to merely document the fact that SQLite allowing NULLs in most PRIMARY KEY columns. " Under SQL Data Constraints at https://sqlite.org/lang_createtable.html --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
These are different requests in SQL. "ID = NULL" is comparing the ID to "NULL". Comparing any value to "NULL" fails. This is equivalent to SELECT ID FROM Tbl WHERE FALSE; A smarter query planner would run it in zero ms. :) "ID IS NULL" is checking if the value in ID is null. Neither will return any values since a primary key can not be NULL. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
Hello, On 2018-01-06 15:22, x wrote: Because the field is not NULL in Luuk's definition and NULL values are not covered by the INDEX. SQLite assumes that you know what you are doing and tries to find NULL values by full scan. The ID field in my definition is also not null. If so, then you should obtain results mentioned by Luuk: sqlite> Explain query plan select ID from Tbl where ID is null order by ID; selectid|order|from|detail 0|0|0|SCAN TABLE Tbl I have the same results. ``SCAN TABLE Tbl USING COVERING INDEX ...'' is returned in case of not ``NOT NULL'' field. ``SCAN TABLE Tbl USING COVERING INDEX ...'' means that your index is used. Order by does not apply as it is the same as PK. Scanning by using PK results in ORDERed BY PK records. There is no need to use separate INDEX for ORDER BY clause. I’m not sure what you’re saying there Cezary. I recognise that the (non-existent) result set will all be NULLs and the order is therefore meaningless but if the query planner recognises that fact why does it not recognise that there will be no result set. Order is meaningful but it is the same as order of a table scanning process -- this is why ORDER BY is ``ignored'' -- not because there will be an empty result set. If you change to ORDER BY ID*2, then you will see that temporary index will be created. The same index is used for scanning and ordering, so there is no need to use it twice. If I run explain query plan select ID from Tbl where 0; // that’s where zero I again get the result SCAN TABLE Tbl USING COVERING INDEX XXX Again, I have the sole ``SCAN TABLE'' (without index -- in both cases: NULLs allowed and NOT NULL). Your result is obtained as if it was ORDER BY clause. However the EXPLAIN for the same query returns addropcode p1p2p3 p4p5comment 0 Init 0 8 0 00 Start at 8 1 Goto 0 7 0 00 2 OpenRead 1 109 0 k(2,,) 00root=109 iDb=0; tID 3 Rewind 1 7 1 0 00 4 IdxRowid1 1 0 00r[1]=rowid 5 ResultRow 1 1 0 00output=r[1] 6 Next1 4 0 01 7 Halt 0 0 0 00 8 Transaction0 0 392 0 01usesStmtJournal=0 9 Goto 0 1 0 00 which, if I’m reading it correctly, executes 0, 8, 9, 1 & 7 (Halt) and so comes up with the empty result set in a few milliseconds. That contrasts with the EXPLAINs of the IS NULL queries mentioned earlier which do an actual table scan. Indeed -- I have nearly the same: SQLite jumps directly to Halt in case of WHERE 0, except that I have Column instead of IdxRowid. Could you provide your table's CREATE command? AFAIR you are using SQLite 3.21, are not you? -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
Thanks Cezary but I’m none the wiser. >Because the field is not NULL in Luuk's definition and NULL values are >not covered by the INDEX. SQLite assumes that you know what you are >doing and tries to find NULL values by full scan. The ID field in my definition is also not null. >``SCAN TABLE Tbl USING COVERING INDEX ...'' means that your index is >used. Order by does not apply as it is the same as PK. Scanning by using >PK results in ORDERed BY PK records. There is no need to use separate >INDEX for ORDER BY clause. I’m not sure what you’re saying there Cezary. I recognise that the (non-existent) result set will all be NULLs and the order is therefore meaningless but if the query planner recognises that fact why does it not recognise that there will be no result set. If I run explain query plan select ID from Tbl where 0; // that’s where zero I again get the result SCAN TABLE Tbl USING COVERING INDEX XXX However the EXPLAIN for the same query returns addropcode p1p2p3 p4p5comment 0 Init 0 8 0 00 Start at 8 1 Goto 0 7 0 00 2 OpenRead 1 109 0 k(2,,) 00root=109 iDb=0; tID 3 Rewind 1 7 1 0 00 4 IdxRowid1 1 0 00r[1]=rowid 5 ResultRow 1 1 0 00output=r[1] 6 Next1 4 0 01 7 Halt 0 0 0 00 8 Transaction0 0 392 0 01usesStmtJournal=0 9 Goto 0 1 0 00 which, if I’m reading it correctly, executes 0, 8, 9, 1 & 7 (Halt) and so comes up with the empty result set in a few milliseconds. That contrasts with the EXPLAINs of the IS NULL queries mentioned earlier which do an actual table scan. Regards Tom From: sqlite-users on behalf of Cezary H. Noweta Sent: Saturday, January 6, 2018 1:01:13 PM To: SQLite mailing list Subject: Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL' Hello, On 2018-01-06 13:33, x wrote: >> sqlite> Explain query plan select ID from Tbl where ID is null order by ID; >> selectid|order|from|detail >> 0|0|0|SCAN TABLE Tbl > > Luuk, I’m also using 3.21. Presumably if you give your Tbl a secondary index > XXX then the explain query plan will match mine (SCAN TABLE Tbl USING > COVERING INDEX XXX). Your example muddies the water further though. Why is it > scanning an entire table when it could scan the pk? Because the field is not NULL in Luuk's definition and NULL values are not covered by the INDEX. SQLite assumes that you know what you are doing and tries to find NULL values by full scan. >>> It doesn’t even use the ID pk despite the fact it’s the requested order by?? >> That is explained by Cezary, if the explanation was unclear answer to his >> post... > > I wasn’t specifically replying to your post, I just clicked reply to the > latest reply. I don’t see where Cezary explains it though. In your example (a field allowing NULLs) PK is used: 2 Null 0 1 000 3 Affinity 1 1 0 D 00 4 SeekGE 1 9 1 1 00 5 IdxGT 1 9 1 1 00 6 Column 1 0 200 7 ResultRow 2 1 000 8 Next 1 5 000 9 Halt 0 0 000 ``SCAN TABLE Tbl USING COVERING INDEX ...'' means that your index is used. Order by does not apply as it is the same as PK. Scanning by using PK results in ORDERed BY PK records. There is no need to use separate INDEX for ORDER BY clause. -- best regards Cezary H. Noweta ___ 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
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
Hello, On 2018-01-06 13:33, x wrote: sqlite> Explain query plan select ID from Tbl where ID is null order by ID; selectid|order|from|detail 0|0|0|SCAN TABLE Tbl Luuk, I’m also using 3.21. Presumably if you give your Tbl a secondary index XXX then the explain query plan will match mine (SCAN TABLE Tbl USING COVERING INDEX XXX). Your example muddies the water further though. Why is it scanning an entire table when it could scan the pk? Because the field is not NULL in Luuk's definition and NULL values are not covered by the INDEX. SQLite assumes that you know what you are doing and tries to find NULL values by full scan. It doesn’t even use the ID pk despite the fact it’s the requested order by?? That is explained by Cezary, if the explanation was unclear answer to his post... I wasn’t specifically replying to your post, I just clicked reply to the latest reply. I don’t see where Cezary explains it though. In your example (a field allowing NULLs) PK is used: 2 Null 0 1 000 3 Affinity 1 1 0 D 00 4 SeekGE 1 9 1 1 00 5 IdxGT 1 9 1 1 00 6 Column 1 0 200 7 ResultRow 2 1 000 8 Next 1 5 000 9 Halt 0 0 000 ``SCAN TABLE Tbl USING COVERING INDEX ...'' means that your index is used. Order by does not apply as it is the same as PK. Scanning by using PK results in ORDERed BY PK records. There is no need to use separate INDEX for ORDER BY clause. -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
>sqlite> Explain query plan select ID from Tbl where ID is null order by ID; >selectid|order|from|detail >0|0|0|SCAN TABLE Tbl Luuk, I’m also using 3.21. Presumably if you give your Tbl a secondary index XXX then the explain query plan will match mine (SCAN TABLE Tbl USING COVERING INDEX XXX). Your example muddies the water further though. Why is it scanning an entire table when it could scan the pk? >> It doesn’t even use the ID pk despite the fact it’s the requested order by?? >That is explained by Cezary, if the explanation was unclear answer to his >post... I wasn’t specifically replying to your post, I just clicked reply to the latest reply. I don’t see where Cezary explains it though. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
42 ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
On 06-01-18 10:44, x wrote: > Thanks for the replies. > > While I’m now on board as to what a NULL is I’m still a bit puzzled by the > query planner. > > Explain query plan select ID from Tbl where ID is null order by ID; > > returns > > SCAN TABLE Tbl USING COVERING INDEX ... > > I do see different things: sqlite> .version SQLite 3.21.0 2017-10-24 18:55:49 1a584e499906b5c87ec7d43d4abce641fdf017c42125b083109bc77c4de4alt2 sqlite> .schema tbl CREATE TABLE tbl (id integer primary key not null); sqlite> select count(*) from tbl; count(*) 8388608 sqlite> Explain query plan select ID from Tbl where ID is null order by ID; selectid|order|from|detail 0|0|0|SCAN TABLE Tbl sqlite> > It doesn’t even use the ID pk despite the fact it’s the requested order by?? That is explained by Cezary, if the explanation was unclear answer to his post... ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
On Sat, 6 Jan 2018 09:53:46 +0100, Luuk wrote: > On 06-01-18 00:49, Simon Slavin wrote: >> >> To expand on this, in SQL NULL has a special meaning. > > This should read: > NULL has a special meaning. > and not: > in SQL NULL has a special meaning. > > Because the use of NULL is not 'reserverd' for SQL, and in SQL it is not > more special than in any other environment. I don't understand what you mean. NULL has a special meaning in SQL (Structured Query Language), and that is what we are talking about. Eric -- ms fnd in a lbry ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
Thanks for the replies. While I’m now on board as to what a NULL is I’m still a bit puzzled by the query planner. Explain query plan select ID from Tbl where ID is null order by ID; returns SCAN TABLE Tbl USING COVERING INDEX ... It doesn’t even use the ID pk despite the fact it’s the requested order by?? > What has you checking an integer primary key for null anyway? David, to test something I needed a query that returned 0 rows and I modified the query I was already working on rather than type out a new select. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
On 06-01-18 00:49, Simon Slavin wrote: > > To expand on this, in SQL NULL has a special meaning. This should read: NULL has a special meaning. and not: in SQL NULL has a special meaning. Because the use of NULL is not 'reserverd' for SQL, and in SQL it is not more special than in any other environment. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
Hello, On 2018-01-05 21:56, David Raymond wrote: sqlite> explain select id from tbl where id = null; addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 8 000 Start at 8 1 OpenRead 0 3 0 0 00 root=3 iDb=0; tbl 2 Explain0 0 0 SEARCH TABLE tbl USING INTEGER PRIMARY KEY (rowid=?) 00 3 Null 0 1 000 r[1]=NULL 4 SeekRowid 0 7 100 intkey=r[1]; pk 5 Copy 1 2 000 r[2]=r[1] 6 ResultRow 2 1 000 output=r[2] 7 Halt 0 0 000 8 Transaction0 0 2 0 01 usesStmtJournal=0 9 Goto 0 1 000 Run Time: real 0.000 user 0.00 sys 0.00 ??? My 3.21 checks if NULL is NULL and exits: 2 Null 0 1 3 IsNull 1 9 ... 9 Halt 0 0 -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
On 5 Jan 2018, at 9:03pm, Doug Currie wrote: > NULL is not equal to NULL, though NULL is NULL. > > sqlite> select NULL IS NULL; > > 1 > > sqlite> select NULL = NULL; > > > sqlite> To expand on this, in SQL NULL has a special meaning. It means "value unknown or missing". It turns up naturally in some places, for example, in JOINs where the joined row doesn’t exist. Don’t think of NULL as a value. Think of it as a flag. Because of this, testing for NULL is unexpectedly complicated, as shown by the above. Two values might both be unknown, but this doesn’t automatically mean that they have the same value. Yet an unknown value is indeed unknown, even if we don’t know what it is. That’s why you get the results Doug posted above. Many examples I see which store NULL as a value in the database don’t really mean NULL, they mean 0 or the empty string, or an empty list, and changing the database to store a more appropriate value lets the programmer strip many lines of complicated logic out of their software. Simon. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
Cezary is correct, NULL is not equal to NULL, though NULL is NULL. sqlite> select NULL IS NULL; 1 sqlite> select NULL = NULL; sqlite> e ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
Hwllo, On 2018-01-05 21:19, x wrote: Tbl has 2.4 million records and has an INTEGER PRIMARY KEY ID which is NOT NULL. SELECT ID FROM Tbl where ID = NULL It has no sense as ``='' returns NULL when one of operands is NULL. NULL evaluates to FALSE. Above query can be described as SELECT NOTHING. 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. SQLite is looking for NULL values of ID. Please, please, do not require to optimize such query due to the fact that your field does not contain NULLs. :-) I’m sure I’ve read something before about this but can’t find it in the documentation? STH IS STH allows NULLs and returns 1 if both are NULLs, 0 otherwise. http://sqlite.org/lang_expr.html#isisnot -- best regards Cezary H. Noweta ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] difference between 'ID IS NULL' and 'ID = NULL'
I 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 p1p2p3p4 p5 comment - - -- - 0 Init 0 8 000 Start at 8 1 OpenRead 0 3 0 0 00 root=3 iDb=0; tbl 2 Explain0 0 0 SEARCH TABLE tbl USING INTEGER PRIMARY KEY (rowid=?) 00 3 Null 0 1 000 r[1]=NULL 4 SeekRowid 0 7 100 intkey=r[1]; pk 5 Copy 1 2 000 r[2]=r[1] 6 ResultRow 2 1 000 output=r[2] 7 Halt 0 0 000 8 Transaction0 0 2 0 01 usesStmtJournal=0 9 Goto 0 1 000 Run Time: real 0.000 user 0.00 sys 0.00 sqlite> explain select id from tbl where id is null; addr opcode p1p2p3p4 p5 comment - - -- - 0 Init 0 10000 Start at 10 1 OpenRead 0 3 0 0 00 root=3 iDb=0; tbl 2 Explain0 0 0 SCAN TABLE tbl 00 3 Rewind 0 9 000 4 Rowid 0 1 000 r[1]=rowid 5 NotNull1 8 000 if r[1]!=NULL goto 8 6 Copy 1 2 000 r[2]=r[1] 7 ResultRow 2 1 000 output=r[2] 8 Next 0 4 001 9 Halt 0 0 000 10Transaction0 0 2 0 01 usesStmtJournal=0 11Goto 0 1 000 -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
[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