Hello,
To summarize:
On 2018-01-07 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?
OK - your creation statement causes that ``ID'' is an alias for
``rowid''. Why PK is not used, was mentioned by me:
On 2018-01-06 14:01, Cezary H. Noweta 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.
and Scott:
On 2018-01-07 20:30, Scott Robison wrote:
Integer primary key is by definition not null, so looking for a null value
on an index can't work.
Look at ``where.c:whereLoopAddBtreeIndex()'':
if( (eOp==WO_ISNULL || (pTerm->wtFlags&TERM_VNULL)!=0)
&& indexColumnNotNull(pProbe, saved_nEq)
){
continue; /* ignore IS [NOT] NULL constraints on NOT NULL columns */
}
Even if you used ``ID INT PRIMARY...'' instead of ``ID INTEGER
PRIMARY...'' (which did not aliased ``rowid'' to ``ID'') results would
be the same, because ``ID'' is not NULL.
Now we are considering a full scan. As Luuk wrote:
On 2018-01-07 18:55, Luuk 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;'
Calculating a cost of a DB's searching is a bit complicated and cannot
be described in two words. You could start at a comment of
``where.c:whereLoopAddBtree()''. When your ratio of a length of an index
element to a length of a table row has been lowered, then index ``XXX''
is chosen as the most effective solution. (``rowid'' pseudo-index'
entries are considered as being equal to a whole row of a table).
However this index is used for a full scan -- not for a binary/log_2n
searching for values: ``Yahoooooo! My query is using an index so it
achieved a speed of light''. If it was true, then certainly ``XXX''
would be used earlier.
If you want to enforce some index to be used you will have to append
``INDEXED BY'' clause, however:
1. Choosing a bad index will result in no index at all.
2. As Scott noticed:
On 2018-01-07 20:30, Scott Robison wrote:
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.
For the purpose of ``SELECT'' there is a pseudo-index created for
``rowid'' -- it is impossible to enforce that pseudo-index. Unless you
use ``INT'' instead of ``INTEGER'' in your ``CREATE TABLE'' statement
(which will leave ``rowid'', and ``ID'' will not be alias for ``rowid'')
-- such primary index will be named ,,sqlite_autoindex_<tablename>_<n>''
-- you will have to guess <n> (or look into a db-file, or -- at best --
SELECT from ``sqlite_master'').
Primary key ``rowid'' (or alias) is not indexed, only ``primary keys''
other then ``rowid'' are. In fact it is impossible to have NULL
``rowid'' (or alias) in destroyed db -- due to a fact that it has NOT
NULL, int64 (variable length) format -- there is no place for ``I'm a
NULL'' flag. Non-rowid, NOT NULL, primary keys with NULL value could be
occurring eventually.
I hope, above will help and dispel any doubts.
-- best regards
Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
