> That is, is leaving it to the > query optimiser to figure out that I only need the sub select once the > best thing to do?
AFAIK, SQLite's optimizer is not that smart to collapse two identical sub-queries and reuse once generated result. > Is the select I'm doing where both a_id1 & 2 are "in" the exact same > select the most efficient way to do this? I'd say that the following query will work faster in this particular case (with this set of tables and indexes): select ar.* from a_relation ar, ab ab1, ab ab2 where ar.a_id1 = ab1.a_id and ab1.b_id = 1 and ar.a_id2 = ab2.a_id and ab2.b_id = 1; But this query could be not transformable to your real case. Also performance in real schema could be different. Pavel On Tue, May 17, 2011 at 5:29 AM, Matthew Jones <matthew.jo...@hp.com> wrote: > O.k. So this is a very cut down example but it illustrates the question: > > sqlite> create table a (a_id int primary key); > sqlite> create table b (b_id int primary key); > sqlite> create table ab (a_id int, b_id int, primary key(a_id, b_id)); > sqlite> create table a_relation (a_id1 int, a_id2, primary key(a_id1, > a_id2)); > sqlite> select * from a_relation where > ...> a_id1 in (select a_id from ab where b_id = 1) and > ...> a_id2 in (select a_id from ab where b_id = 1); > > Is the select I'm doing where both a_id1 & 2 are "in" the exact same > select the most efficient way to do this? That is, is leaving it to the > query optimiser to figure out that I only need the sub select once the > best thing to do? > > (The actual tables in question are a little more complicated and I have > versions to cope with but this effectively what I'm doing in C++ [so I'm > preparing and binding etc.]. The actual sub select have a group by a_id > to cope with multiple entries with different versions.) > > Thanks > > -- > Matthew Jones > Hewlett-Packard Ltd > _______________________________________________ > 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
