Hello,
On 2018-01-06 13:33, x wrote:
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
Luuk, I’m also using 3.21. Presumably if you give your Tbl a secondary index
XXX then the explain query plan will match mine (SCAN TABLE Tbl USING COVERING
INDEX XXX). Your example muddies the water further though. Why is it scanning
an entire table when it could scan the pk?
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.
It doesn’t even use the ID pk despite the fact it’s the requested order by??
That is explained by Cezary, if the explanation was unclear answer to his
post...
I wasn’t specifically replying to your post, I just clicked reply to the latest
reply. I don’t see where Cezary explains it though.
In your example (a field allowing NULLs) PK is used:
2 Null 0 1 0 00
3 Affinity 1 1 0 D 00
4 SeekGE 1 9 1 1 00
5 IdxGT 1 9 1 1 00
6 Column 1 0 2 00
7 ResultRow 2 1 0 00
8 Next 1 5 0 00
9 Halt 0 0 0 00
``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.
-- best regards
Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
