First of all I couldn't for the life of we work out why that new query would work but I'm sure that's just a limit of my knowledge. I then realised that the database definition I had used was really very different from what I was trying to do so I've had another go and then tried to use the query with the new schema. Unfortunately, I have failed miserably so here's the schema I'm working with now.
Note that table b isn't used by I've created it to show that table ab is a list of references of a records to b records and b records contain an a_id sqlite> create table a (a_id int primary key); sqlite> create table b (a_id int, b_id int, primary key(a_id, b_id)); sqlite> insert into a values(1); sqlite> insert into a values(2); sqlite> create table aa (a_id1 int, a_id2 int, primary key(a_id1, a_id2)); sqlite> insert into aa values (1, 1); sqlite> insert into aa values (1, 2); sqlite> insert into aa values (2, 1); sqlite> insert into aa values (3, 1); sqlite> insert into aa values (1, 3); sqlite> select * from aa; 1|1 1|2 2|1 3|1 1|3 sqlite> create table ab (a_id1 int, a_id2 int, b_id int, primary key(a_id1, a_id2, b_id)); sqlite> insert into ab values(1, 1, 1); sqlite> insert into ab values(2, 1, 1); sqlite> select * from aa where a_id1 in (select distinct a_id1 from ab where ab.a_id2 = 1 and ab.b_id = 1) and a_id2 in (select distinct a_id1 from ab where ab.a_id2 = 1 and ab.b_id = 1) ; 1|1 1|2 2|1 sqlite> select aa.* from aa ar, ab ab1, ab ab2; Error: no such table: aa sqlite> select aa.* from aa as ar, ab ab1, ab ab2; Error: no such table: aa sqlite> At this point I got confused but realised I had asked about a very different schema and obviously must be doing something wrong as this failed before adding any sort of where clause. > From: Pavel Ivanov<[email protected]> > Subject: Re: [sqlite] Query efficiency > >> > 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<[email protected]> 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 >> > Thanks -- Matthew Jones Hewlett-Packard Ltd _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
