>> So it seems that the subselect is preventing the ordering index from >> being used. What's interesting is that if I select the ids in a >> different sql statement, concatenate them together in a big list of >> numbers, and supply that in the extra statement, the ordering index >> is still not used, but the query runs twice as fast! > > Is it still faster if you include the time of running that separate > statement for selecting IDs?
Good point - I wasn't accounting for that. After factoring it in, the two step operation is still a little faster, but only by 10% or so now. As for sqlite using only one index per table, I've had to work around that previously, and it unfortunately leads to large indices in my case since I end up creating indices like: a, b, c, x a, b, c, y a, b, c, z so that I may do where a = 1 and b = 2 and c = 3 order by a,b,c, x IIRC firebird is able to use multiple indices on the one table. Is there a reason that sqlite doesn't? Is it too complicated? Don't get me wrong - I find sqlite extremely useful, and I'm just curious as to the reasons why it uses the current design. Cheers, Damien _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
