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
