MySQL Forums :: Performance :: How to speed up Query with WHERE AND/OR, with Subqueries using IN


Advanced Search

How to speed up Query with WHERE AND/OR, with Subqueries using IN
Posted by: Martin Dingel ()
Date: May 17, 2010 04:25AM

Hello MysqlCracks,

i found myself having a severe performance problem with the following query and I'm desperately seeking for help...

Lets have the following query:

---------------------------------------
SELECT distinct p.productID
FROM productattributes p
WHERE 1=1

AND (
p.productID in (SELECT distinct productID FROM productattributes WHERE (atrID = 'A00056' and valID = 'A'))
AND
p.productID in (SELECT distinct productID FROM productattributes WHERE (atrID = 'A03413' and valID = 'B'))
)
OR(
p.productID in (SELECT distinct productID FROM productattributes WHERE (atrID = 'A00056' and valID = 'A'))
AND
p.productID in (SELECT distinct productID FROM productattributes WHERE (atrID = 'A03413' and valID = 'C'))
)
OR(
p.productID in (SELECT distinct productID FROM productattributes WHERE (atrID = 'A00056' and valID = 'A'))
AND
p.productID in (SELECT distinct productID FROM productattributes WHERE (atrID = 'A03413' and valID = 'D'))
);
---------------------------------------

The table "productattributes" contains several Attribut-Value-Entries for a specific product, e.g:

Product "12345" AtributeID "Size" AtributeValue "1GB"
Product "12345" AtributeID "Performance" AtributeValue "800MHz"

and so on. This data is used to assign products to a category, e.g. "this category contains DDR2 RAM with 800MHz Speed, and also DDR2 RAM with 1066MHz speed"

In order to do this I need to resolve all products from this single table that fit in one of the valid atribute/value combinations.

In my example there are three different combos (and "x" or "y" or "z") to resolve a valid set of productIDs having one of the required atrib/value combinations.

My query is incredibly slow and will need about 5 secs for 19 products from a table with 160.000 records...

What can i do to speed this up? Any help is greatly appreciated...

Thanks a lot,

Martin

Options: ReplyQuote


Subject Views Written By Posted
How to speed up Query with WHERE AND/OR, with Subqueries using IN 3065 Martin Dingel 05/17/2010 04:25AM
Re: How to speed up Query with WHERE AND/OR, with Subqueries using IN 1021 Rick James 05/18/2010 09:24AM
Re: How to speed up Query with WHERE AND/OR, with Subqueries using IN 1059 Martin Dingel 05/21/2010 03:21AM
Re: How to speed up Query with WHERE AND/OR, with Subqueries using IN 1128 Rick James 05/21/2010 09: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.