CODEWITHSUNDEEP
sqlite
richvdh
richvdh

Reputation: 1253

SQLite: Not using PRIMARY KEY column in an index

I have the following tables in a test SQLite database:

CREATE TABLE "test" (ord INTEGER, room TEXT NOT NULL);
CREATE INDEX test_room_idx on test(room, ord);

CREATE TABLE "test2" (ord INTEGER PRIMARY KEY, room TEXT NOT NULL);
CREATE INDEX test2_room_idx on test2(room, ord);

The data in both tables is identical: about 180000 rows, of which 150000 have room='zO'.

The problem is that a query on test2 for a given room and a small range of ords will not use the ord column of test2_room_idx:

sqlite> explain query plan SELECT * FROM test WHERE room = 'zO' AND ord between 500000 and 501000;
selectid    order       from        detail                                                                           
----------  ----------  ----------  ---------------------------------------------------------------------------------
0           0           0           SEARCH TABLE test USING COVERING INDEX test_room_idx (room=? AND ord>? AND ord<?)
sqlite> explain query plan SELECT * FROM test2 WHERE room = 'zO' AND ord between 500000 and 501000;
selectid    order       from        detail                                                         
----------  ----------  ----------  ---------------------------------------------------------------
0           0           0           SEARCH TABLE test2 USING COVERING INDEX test2_room_idx (room=?)

For 150000 rows, this isn't too bad, but my production data has millions of rows, and the query ends up taking seconds.

I have ANALYZEd both tables. The problem appears to be specific to columns which match the INTEGER PRIMARY KEY requirements for rowid.

My questions are:

Upvotes: 1

Views: 96

Answers (1)

CL.
CL.

Reputation: 180070

Both queries are optimized correctly in recent SQLite versions, so apparently this was a (now-fixed) bug.

Just update your SQLite.

Upvotes: 2

Related Questions