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 <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
Scott Robison <sc...@casaderobison.com>
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" <luu...@gmail.com> 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

Reply via email to