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
