Sergey M. Brytsko <[EMAIL PROTECTED]>
wrote:
I have the following DB schema:
CREATE TABLE XXX(AAA TEXT, BBB INTEGER);
CREATE INDEX AAA_IDX ON XXX(AAA);
CREATE INDEX BBB_IDX ON XXX(BBB);
SQLite 3.4.0
The problem is the index is NOT used for query:
SELECT BBB FROM XXX WHERE BBB <> 100;
An index is not helpful for this query.
but in case of query
SELECT BBB FROM XXX WHERE BBB > 100;
all is ok
Note that, if most records have BBB>100, using the index might actually
be slower than a full scan of the table.
The same problem:
SELECT AAA FROM XXX WHERE AAA IN ('QWERTY'); // index used
SELECT AAA FROM XXX WHERE AAA NOT IN ('QWERTY'); // index not used
Same reason - an index cannot speed up inequality test.
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
