I overly simplified my example. I'm actually selecting columns from table B and C which is why I had this in the where clause:
AND ( JoinAToB.B_id = B.id ) AND ( JoinAToB.A_id = A.id ) ... Converting those to explicit JOIN clauses fixed the problem. Thanks for your help! -----Original Message----- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: Monday, February 12, 2007 4:10 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Performance Question Slater, Chad wrote: > Hello, > > I'm having trouble with the performance of one of my queries and my "sql > kung fu" is limited. Any help with this problem would be greatly > appreciated > > Here's a stripped down version of the tables I'm dealing with: > > CREATE TABLE A ( > id INTEGER PRIMARY KEY AUTOINCREMENT, > name TEXT > ) > > CREATE TABLE B ( > id INTEGER PRIMARY KEY AUTOINCREMENT, > name TEXT > ) > > CREATE TABLE C ( > id INTEGER PRIMARY KEY AUTOINCREMENT, > name TEXT > ) > > CREATE TABLE JoinAToB ( > A_id INTEGER NOT NULL REFERENCES A ( id ), > B_id INTEGER NOT NULL REFERENCES B ( id ), > UNIQUE( A_id, B_id ) > ) > > CREATE TABLE JoinAToC ( > A_id INTEGER NOT NULL REFERENCES A ( id ), > C_id INTEGER NOT NULL REFERENCES C ( id ), > UNIQUE( A_id, C_id ) > ) > > The following query takes so long I end up killing the app before the > query returns: > > SELECT DISTINCT A.id > FROM A, > C, > B, > JoinAToB, > JoinAToC > WHERE > ( ( ( JoinAToB.B_id IN ( 1 ) ) > AND ( JoinAToB.B_id = B.id ) > AND ( JoinAToB.A_id = A.id ) ) > > OR ( ( JoinAToC.C_id IN ( 1 ) ) > AND ( JoinAToC.C_id = C.id ) > AND ( JoinAToC.A_id = A.id ) ) ) ; > > > Table A has approx 13,000 rows > Table B has 15 rows > Table C has 5 row > JoinTableAToB has 11 rows > JoinTableAToC has approx 450 rows > > If I execute either of these queries separately they are very fast: > > SELECT DISTINCT A.id > FROM A, B, > JoinAToB > WHERE > JoinAToB.B_id IN ( 1 ) AND JoinAToB.B_id = B.id AND JoinAToB.A_id > = A.id ; > > > SELECT DISTINCT A.id > FROM A, > C, > JoinAToC > WHERE > JoinAToC.C_id IN ( 1 ) AND JoinAToC.C_id = C.id AND JoinAToC.A_id > = A.id ; > > > Adding the OR clause to combine the results seems to be the culprit but > I don't know why... > Chad, You seem to be overly complicating the matter. Your query SELECT DISTINCT A.id FROM A, B, JoinAToB WHERE JoinAToB.B_id IN ( 1 ) AND JoinAToB.B_id = B.id AND JoinAToB.A_id = A.id ; is the same as SELECT A_id FROM JoinAToB WHERE JoinAToB.B_id = 1; Your table JoinAToB relates some A ids to some B ids. You don't need to join this to the tables A and B to do a query on the ids in that table. Similarly arguments apply to your table JoinAToC. It looks like you are trying to get all the A ids that are referenced by these two tables where the B id is 1 or the C id is 1. In SQL this is: SELECT A_id FROM JoinAToB WHERE JoinAToB.B_id = 1 UNION SELECT A_id FROM JOINAToC WHERE JoinAToC.C_id = 1 HTH Dennis Cote ------------------------------------------------------------------------ ----- To unsubscribe, send email to [EMAIL PROTECTED] ------------------------------------------------------------------------ ----- ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
