On Fri, May 17, 2013 at 10:26 AM, Konstantinos Alogariastos < marau...@gmail.com> wrote:
> 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. > Collating orders and affinities might be disqualifying the constraint on "id" from being used with the index. > > > 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 > -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
