I am already aware of the contents of the query planner documentation. As you say, only one b-tree index will be used. What I want to achieve is what is described in the documentation " *The second column is used to break ties in the left-most column* ".
In my case, my custom index "idx_test(class,id)" should first search in the "class" column and if there are any ties there (which happens a lot in my case), the "id" column should be used to break them. However, as evidenced by the query plan output, only the "class" column is used for indexing. Best regards, Kostas On May 17, 2013, at 10:54 AM, Konstantinos Alogariastos <marauber at gmail.com <http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users>> wrote: >* Does this mean that one cannot use a index on two columns when in the >query*>* both columns are used with "IN"?* You might want to read up on the query planner: http://www.sqlite.org/queryplanner.html#searching The short of it: only one btree index will be used per source table. >* Is this a limitation of SQLite or a bug?* Neither. But read up on Multi-Column Indices. There exist other types of indexes (for example bitmap indexes [1]), which have different properties and can be combined to resolve a query, but such structures are not supported by SQLite. [1] http://en.wikipedia.org/wiki/Bitmap_index 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
