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 > firstname.lastname@example.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list email@example.com http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users