MySQL Forums
Forum List  »  Performance

Re: How to speed up Query with WHERE AND/OR, with Subqueries using IN
Posted by: Martin Dingel
Date: May 21, 2010 03:21AM

Hi Rick,

thanks for your help. I have found another possible solution for my problem, by redesigning the statement, and using UNION and HAVING Statements. Now i have three single statements cleaned up with UNION DISTINCT instead of one big query with slow subs...

---------------
SELECT p.productID FROM productattributes p WHERE (atrID = 'A00056' and valID = 'A') OR (atrID = 'A03413' and valID = 'B') GROUP BY p.productID HAVING COUNT(p.productID) = 2
UNION DISTINCT
SELECT p.productID FROM productattributes p WHERE (atrID = 'A00056' and valID = 'A') OR (atrID = 'A03413' and valID = 'B') GROUP BY p.productID HAVING COUNT(p.productID) = 2
UNION DISTINCT
SELECT p.productID FROM productattributes p WHERE (atrID = 'A00056' and valID = 'A') OR (atrID = 'A03413' and valID = 'C') GROUP BY p.productID HAVING COUNT(p.productID) = 2;
---------------

This is much more faster... below 1 sec for about 100 results out of my 160.000 records table.

For the moment thats okay, but i will think about storing data in a more performance optimized way by using optimized table designs for search purpose...

Regards,

Martin

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: How to speed up Query with WHERE AND/OR, with Subqueries using IN
1298
May 21, 2010 03:21AM


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.