How to speed up Query with WHERE AND/OR, with Subqueries using IN

[Martin Dingel]

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