>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

Reply via email to