Does SQLite choose wrong query plan when rowID is the second column of an index

execution-planindexsqlite

I was testing the performance of my DB and found that SQLite wasn't choosing the right query plan for my query.

My schema:

CREATE TABLE event (
    ID       INTEGER PRIMARY KEY NOT NULL,
    netAddr  INTEGER             NOT NULL,
    date     INTEGER             NOT NULL,
    value    INTEGER             NOT NULL);
INSERT INTO event ( netAddr, date, value )
    WITH RECURSIVE cnt(x) as (
        VALUES(1)
        UNION ALL
        SELECT x+1 FROM cnt LIMIT 1000000
    ) SELECT 250, x, x+1000 FROM cnt;
CREATE INDEX myIdx ON event ( netAddr, ID );

The query:

SELECT value FROM event
WHERE netAddr = 250 AND ID > 100 AND ID < 110
ORDER BY ID DESC LIMIT 1;

It ran too slow and I inspected the query plan:

EXPLAIN QUERY PLAN
SELECT value FROM event
WHERE netAddr = 250 AND ID > 100 AND ID < 110
ORDER BY ID DESC LIMIT 1;

which gave me

0|0|0|SEARCH TABLE event USING INDEX myIdx (netAddr=?)

SQLite refuses to use the second column of the index even though it is sorted the way that the query wants it to be.

Then I tested with a different index:

CREATE INDEX dateIdx ON event ( netAddr, date );
EXPLAIN QUERY PLAN
SELECT value FROM event
WHERE netAddr = 250 AND date > 100 AND date < 110
ORDER BY date DESC LIMIT 1;

which gave me

0|0|0|SEARCH TABLE event USING INDEX dateIdx (netAddr=? AND date>? AND date<?)

The query plan is now as I expect it to be.

  1. Why doesn't SQLite use the whole of the index i.e. SEARCH TABLE event USING INDEX myIdx (netAddr=? AND ID>? AND ID<?)?
  2. What is wrong with having the rowID as a second column in an index?
  3. Is there a way to make it work without adding another column in the table or in the index? (I have little storage for the DB.)

EDIT:

Tried with SELECT value FROM event WHERE id = (SELECT id FROM event WHERE netAddr = 250 AND id > 100 AND id < 110 ORDER BY id DESC LIMIT 1) LIMIT 1; and SELECT value FROM event WHERE netAddr = 250 AND ID > 100 AND ID < 110 ORDER BY netAddr DESC, ID DESC LIMIT 1; as ypercubeᵀᴹ proposed – the results are the same as the "slow" query.

Tried to change the name of ID column to be something else – the results are the same.

EDIT 2:

SQLite version 3.8.2 and 3.8.8.1

Best Answer

In theory, there's nothing wrong with having the rowid as a second column in an index.
And in practice, with the current SQLite version, it works as you expected it to:

sqlite> .eqp on
sqlite> SELECT value FROM event
   ...> WHERE netAddr = 250 AND ID > 100 AND ID < 110
   ...> ORDER BY ID DESC LIMIT 1;
--EQP-- 0,0,0,SEARCH TABLE event USING INDEX myIdx (netAddr=? AND ID>? AND ID<?)
Related Question