Thank you all for your answers. It seems indeed that the PRIMARY KEY column is causing the problem. The rationale behind using both "Id" and "class" in the index is that, in my actual use case, class can have only 5 distinct values, while Id will reach to the millions, and the table cannot be modified. Also, the constraint for class is IN(0,1), while for id is IN ( "a large list of integers inside here"). Therefore I thought it made more sense to first filter on class and then on id. Now that I see that this is not possible I should rethink my query.
Best regards, Kostas 2013/5/17 Richard Hipp <d...@sqlite.org> > On Fri, May 17, 2013 at 11:02 AM, GB <gbi...@web.de> wrote: > > > > > Richard Hipp schrieb am 17.05.2013 16:37: > > > > Collating orders and affinities might be disqualifying the constraint on > >> "id" from being used with the index. > >> > > It just came to my mind that "id" is an INTEGER PRIMARY KEY column and as > > such is part of every index anyway. Could it be that the additional "id" > > part of that index is silently ignored in this case? > > > > Yes. If "id" is the INTEGER PRIMARY KEY then that messes up everything. > Don't do that. Change the index to omit the "id" and you'll get better > results. > > Furthermore, the "id" is unique so if there is a constraint on the "id" the > query planner will always use that constraint to look up the rows in the > table directly, rather than going through an index, since doing so will be > about twice as fast as using an index. > > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
