Hi all,
I am using SQLite 3.7.13 and I am experiencing a problem with using an
index on multiple columns.
Let's assume the following example:
I have a simple table constructed as such:
CREATE TABLE test(
id INTEGER PRIMARY KEY AUTOINCREMENT,
class INTEGER NOT NULL);
and I insert some data to it.
Next I create an index: CREATE INDEX idx_test ON TEST(class,id);
If I try to use the index with a query similar to the following:
EXPLAIN QUERY PLAN
SELECT *
FROM test
WHERE id IN (0,1)
AND class IN (3,4)
I get the output:
0|0|0|SEARCH TABLE test USING INTEGER PRIMARY KEY (rowid=?) (~2 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
0|0|0|EXECUTE LIST SUBQUERY 1
(which means my index is not used)
If I add "INDEXED BY idx_test" on the above query, I get:
0|0|0|SEARCH TABLE test USING COVERING INDEX idx_test (class=?) (~2 rows)
0|0|0|EXECUTE LIST SUBQUERY 1
0|0|0|EXECUTE LIST SUBQUERY 1
which shows that only one column of the index is used for indexing. This
has the side effect of the query taking longer than it should be.
Modifying the order of the columns on the index or in the query didn't help
either.
Does this mean that one cannot use a index on two columns when in the query
both columns are used with "IN"?
Is this a limitation of SQLite or a bug?
Thanks in advance.
Best regards,
Kostas
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
