Does SQLite choose wrong query plan when rowID is the second column of an index? 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 &gt; 100 AND ID &lt; 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 &gt; 100 AND ID &lt; 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 &gt; 100 AND date &lt; 110
ORDER BY date DESC LIMIT 1;


which gave me

0|0|0|SEARCH TABLE event USING INDEX dateIdx (netAddr=? AND date&gt;? AND date&lt;?)


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


Why doesn't SQLite use the whole of the index i.e. SEARCH TABLE event USING INDEX myIdx (netAddr=? AND ID&gt;? AND ID&lt;?)?
What is wrong with having the rowID as a second column in an index?
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 &gt; 100 AND id &lt; 110 ORDER BY id DESC LIMIT 1) LIMIT 1; and SELECT value FROM event WHERE netAddr = 250 AND ID &gt; 100 AND ID &lt; 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
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 &lt; 110
   ...> ORDER BY ID DESC LIMIT 1;
--EQP-- 0,0,0,SEARCH TABLE event USING INDEX myIdx (netAddr=? AND ID>? AND ID&lt;?)