myyis
asked on
MySQL left join performance
Hi,
When I put
OR J.STATUS='J1'
at the end of the below query I got a performance problem. As I understood from the EXPLAIN when the select sees the OR at the where section it goes over all the values of table CON
If I put this to the end
OR (T.ID='' AND J.STATUS='J1')
I got rid of the performance problem and the EXPLAIN shows that query does not go over for all values of CON. Hence I do not get a correct result because it skips the the values related with table JOB
How can I modify the code the get a better performance using OR J.STATUS='J1'
Thank you.
SELECT * FROM CON CV
LEFT JOIN TASK T ON T.ID=CV.CONID AND T.CONID=?
LEFT JOIN JOB J ON J.ID=CV.CONID AND J.CONID=?
WHERE T.STATUS='T1'
When I put
OR J.STATUS='J1'
at the end of the below query I got a performance problem. As I understood from the EXPLAIN when the select sees the OR at the where section it goes over all the values of table CON
If I put this to the end
OR (T.ID='' AND J.STATUS='J1')
I got rid of the performance problem and the EXPLAIN shows that query does not go over for all values of CON. Hence I do not get a correct result because it skips the the values related with table JOB
How can I modify the code the get a better performance using OR J.STATUS='J1'
Thank you.
SELECT * FROM CON CV
LEFT JOIN TASK T ON T.ID=CV.CONID AND T.CONID=?
LEFT JOIN JOB J ON J.ID=CV.CONID AND J.CONID=?
WHERE T.STATUS='T1'
Please post your final query? The query you posted does not have any OR in that.
ASKER
SELECT * FROM CON CV
LEFT JOIN TASK T ON T.ID=CV.CONID AND T.CONID=?
LEFT JOIN JOB J ON J.ID=CV.CONID AND J.CONID=?
WHERE T.STATUS='T1' OR J.STATUS='J1'
LEFT JOIN TASK T ON T.ID=CV.CONID AND T.CONID=?
LEFT JOIN JOB J ON J.ID=CV.CONID AND J.CONID=?
WHERE T.STATUS='T1' OR J.STATUS='J1'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you