On 2018-01-06 13:33, x wrote:
sqlite> Explain query plan select ID from Tbl where ID is null order by ID;
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 

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

Reply via email to