Hello,
On 2018-01-06 15:22, x wrote:
Because the field is not NULL in Luuk's definition and NULL values are
not covered by the INDEX. SQLite assumes that you know what you are
doing and tries to find NULL values by full scan.
The ID field in my definition is also not null.
If so, then you should obtain results mentioned by Luuk:
sqlite> Explain query plan select ID from Tbl where ID is null order by ID;
selectid|order|from|detail
0|0|0|SCAN TABLE Tbl
I have the same results. ``SCAN TABLE Tbl USING COVERING INDEX ...'' is
returned in case of not ``NOT NULL'' field.
``SCAN TABLE Tbl USING COVERING INDEX ...'' means that your index is
used. Order by does not apply as it is the same as PK. Scanning by using
PK results in ORDERed BY PK records. There is no need to use separate
INDEX for ORDER BY clause.
I’m not sure what you’re saying there Cezary. I recognise that the
(non-existent) result set will all be NULLs and the order is therefore
meaningless but if the query planner recognises that fact why does it not
recognise that there will be no result set.
Order is meaningful but it is the same as order of a table scanning
process -- this is why ORDER BY is ``ignored'' -- not because there will
be an empty result set. If you change to ORDER BY ID*2, then you will
see that temporary index will be created. The same index is used for
scanning and ordering, so there is no need to use it twice.
If I run explain query plan select ID from Tbl where 0; // that’s where zero
I again get the result
SCAN TABLE Tbl USING COVERING INDEX XXX
Again, I have the sole ``SCAN TABLE'' (without index -- in both cases:
NULLs allowed and NOT NULL). Your result is obtained as if it was ORDER
BY clause.
However the EXPLAIN for the same query returns
addr opcode p1 p2 p3 p4 p5 comment
0 Init 0 8 0 00 Start at 8
1 Goto 0 7 0 00
2 OpenRead 1 109 0 k(2,,) 00 root=109 iDb=0; tID
3 Rewind 1 7 1 0 00
4 IdxRowid 1 1 0 00 r[1]=rowid
5 ResultRow 1 1 0 00 output=r[1]
6 Next 1 4 0 01
7 Halt 0 0 0 00
8 Transaction 0 0 392 0 01 usesStmtJournal=0
9 Goto 0 1 0 00
which, if I’m reading it correctly, executes 0, 8, 9, 1 & 7 (Halt) and so comes
up with the empty result set in a few milliseconds. That contrasts with the
EXPLAINs of the IS NULL queries mentioned earlier which do an actual table scan.
Indeed -- I have nearly the same: SQLite jumps directly to Halt in case
of WHERE 0, except that I have Column instead of IdxRowid. Could you
provide your table's CREATE command? AFAIR you are using SQLite 3.21,
are not you?
-- best regards
Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
