MySQL Forums
Forum List  »  Quality Assurance

How can I improve my query performance?? Subqueries
Posted by: ioannes
Date: May 11, 2011 12:23PM

Hi guys,
Is there any way to improve the performance of this query below?

SELECT COUNT(DISTINCT(i.chassi_abreviado)) FROM vw_ve_itenscoleta3 i
WHERE i.idclient = 147 AND i.status = 'PROCESSED'
AND i.idtype_operation = 11
AND i.chassi IN
(SELECT i2.chassi FROM vw_ve_itenscoleta3 i2
WHERE i2.idclient = 147 AND i2.status_coleta = 'PROCESSED' AND i2.idtype_operation = 13)
AND i.chassi IN
(SELECT i3.chassi FROM vw_ve_itenscoleta3 i3
WHERE i3.idclient = 147 AND i3.status_coleta = 'PROCESSED' AND i3.idtype_operation = 14)

I need to select the field chassi that is in idtype_operation = 11 but is also in idtype_operation 13 and is also in idtype_operation 14.
I canĀ“t just do this on my first subquery: AND i2.idtype_operation IN (13,14), because if I do this, the chassi might be in idtype_operation 13 OR 14 but it must have been in both idtype_operation 13 and 14.
My issue is: With this query it travels across my table three times and it takes more time to mysql process my query.
Is there anyway to improve my query performance?

Cheers.

Options: ReplyQuote


Subject
Views
Written By
Posted
How can I improve my query performance?? Subqueries
2170
May 11, 2011 12:23PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.