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...
Regards,
Chad
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
