SQLite Forum

Hi all, i write you below some line to reproduce a "strange" behavior I can't understand about using or not index. Why do you think parse engine act like that, and how can I rewrite the query to force sqlite use index?


create table table_a(id INTEGER PRIMARY KEY AUTOINCREMENT,type INTEGER);
CREATE INDEX IF NOT EXISTS index_a ON table_a(type);

explain query plan select * from table_a WHERE id=1;
QUERY PLAN
`--SEARCH TABLE table_a USING INTEGER PRIMARY KEY (rowid=?)
explain query plan select * from table_a WHERE id!=1;
QUERY PLAN
`--SCAN TABLE table_a

explain query plan select * from table_a WHERE id in (1);
QUERY PLAN
`--SEARCH TABLE table_a USING INTEGER PRIMARY KEY (rowid=?)
explain query plan select * from table_a WHERE id not in (1);
QUERY PLAN
`--SCAN TABLE table_a


explain query plan select * from table_a WHERE type=1;
QUERY PLAN
`--SEARCH TABLE table_a USING COVERING INDEX index_a (type=?)
explain query plan select * from table_a WHERE type!=1;
QUERY PLAN
`--SCAN TABLE table_a

explain query plan select * from table_a WHERE type IN (1);
QUERY PLAN
`--SEARCH TABLE table_a USING COVERING INDEX index_a (type=?)

explain query plan select * from table_a WHERE type NOT IN (1);
QUERY PLAN
`--SCAN TABLE table_a