Dear Gerd, It is a valid observation that the query planner picks PRIMARY KEY indexes very often. However, my question is why, even when I force the query to use another index ( e.g. idx_test ON TEST(class,id) ), the query plan only uses one column out of it. For example in the output below ( 0|0|0|SEARCH TABLE test USING COVERING INDEX idx_test (class=?) (~2 rows) ) it is clearly shown that only the "class" column is used for indexing.
My actual use case involves a large table with about 20 columns, with some of them selected, but the filtering and indexing happens similarly to my simple example. Therefore, in my case performance counts a lot. Kostas, you have id defined as INTEGER PRIMARY KEY so id becomes the rowid and thus the index leaf nodes are the table rows themselves. So I/O-wise there should not be much of a difference in your case. But I already noticed in the past that the query planner puts a heavy weight on INTEGER PRIMARY KEY indexes so it always picks those if they are involved in the WHERE-clause, no matter if other indexes would suit better. Running ANALYZE doesn't help since it does not create statistical data for the implicit PRIMARY KEY index. regards Gerd 2013/5/17 Konstantinos Alogariastos <marau...@gmail.com> > 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
