Link to home
Start Free TrialLog in
Avatar of myyis
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'
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Please post your final query? The query you posted does not have any OR in that.
Avatar of myyis
myyis

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'
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of myyis

ASKER

Thank you