[sqlite] Query planner confusion

[Jean-Paul Calderone]

I've hit a case where SQLite is selecting a suboptimal index for my query and I want to 
do something to tell it to use a different index.  I'm familiar with the "+" 
trick to confuse the optimizer but I can't see how it would apply to this case.

The basic problem is that the index I do not want to use 
(statuses_name_when_msg) has all the same columns in it as the index I do want 
to use (statuses_name_msg), plus one additional column ([when]) which appears 
nowhere in the query I am performing.

Here's some self-contained SQL which demonstrates the issue:

.header on
.mode column

CREATE TABLE messages (id INTEGER);
CREATE TABLE statuses (msg INTEGER, [when] INTEGER, name TEXT COLLATE NOCASE);

SELECT "Without indexes";
EXPLAIN QUERY PLAN SELECT * FROM statuses WHERE msg = ? AND name = ?;
EXPLAIN QUERY PLAN SELECT m.id FROM messages AS m, statuses AS s WHERE s.msg = 
m.id AND s.name = ? ;

SELECT "Index on messages(id)";
CREATE INDEX messages_id ON messages(id);
EXPLAIN QUERY PLAN SELECT * FROM statuses WHERE msg = ? AND name = ?;
EXPLAIN QUERY PLAN SELECT m.id FROM messages AS m, statuses AS s WHERE s.msg = 
m.id AND s.name = ? ;

SELECT "Index on statuses(msg)";
CREATE INDEX statuses_msg ON statuses(msg);
EXPLAIN QUERY PLAN SELECT * FROM statuses WHERE msg = ? AND name = ?;
EXPLAIN QUERY PLAN SELECT m.id FROM messages AS m, statuses AS s WHERE s.msg = 
m.id AND s.name = ? ;

SELECT "Index on statuses(name, msg)";
CREATE INDEX statuses_name_msg ON statuses(name, msg);
EXPLAIN QUERY PLAN SELECT * FROM statuses WHERE msg = ? AND name = ?;
EXPLAIN QUERY PLAN SELECT m.id FROM messages AS m, statuses AS s WHERE s.msg = 
m.id AND s.name = ? ;

-- After this index is created, the same query from above uses the new index, 
rather than the old, good one.
SELECT "Index on statuses(name, msg, [when])";
CREATE INDEX statuses_name_when_msg ON statuses(name, [when], msg);
EXPLAIN QUERY PLAN SELECT * FROM statuses WHERE msg = ? AND name = ?;
EXPLAIN QUERY PLAN SELECT m.id FROM messages AS m, statuses AS s WHERE s.msg = 
m.id AND s.name = ? ;

Is there any way to convince SQLite to use the index I want?

Thanks,

Jean-Paul

-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------