Improve MySQL Query with IN Subquery

I hava a table items and a table item_attributes.

For simplicity let's say my table item has a column id and a column name. Of cource there is a index on the id column.

the item_attributes table has the columns id, item_id, attribute_name and attribute_value and an index ON attrubute_name

Now I want to query all items with a specific attribut without using a join.

I do this with the following query:

SELECT *
FROM items i
WHERE i.id IN (
    SELECT item_id
    FROM item_attributes a
    WHERE a.attribute_name = 'SomeAttribute'
      AND a.attribute_value = 'SomeValue'
)

the SubQuery itself runs fast.

If I execute the query itself first and use the result for an IN query

SELECT *
FROM items i
WHERE i.id IN (1,3,5,7,10,...)

it is fast, too.

However, combined the query is very, very slow (>2 secs.) If I investigate the query plan I see why: MySQL does a full table scan on the items table instead of executing the subquery first and using the result for an index query.

1, 'PRIMARY', 'items', 'ALL', '', '', '', '', 149726, 'Using where'
2, 'DEPENDENT SUBQUERY', 'item_attributes', 'index_subquery', 'IDX_ATTRIBUTE_NAME', 'IDX_ATTRIBUTE_NAME', '4', 'func', 1, 'Using where'

Is there a way to optimize this query? I know that the subquery will always return only a small resultset (<100 rows).

Solution

MySQL cannot switch the leading and the driven table in the IN clause. This is going to be corrected in 6.0.

For now, you can rewrite it like this (requires a JOIN):

SELECT  i.*
FROM    (
        SELECT  DISTINCT item_id
        FROM    item_attributes a
        WHERE   a.attribute_name = 'SomeAttribute'
                AND a.attribute_value = 'SomeValue'
        ) ai
JOIN    items i
ON      i.id = ai.item_id

Since you are using the EAV model you may want to make a unique index on (attribute_name, item_id) in which case you can use a plain join:

SELECT  i.*
FROM    item_attributes ai
JOIN    items i
ON      i.id = ai.item_id
WHERE   a.attribute_value = 'SomeValue'
        AND a.attribute_name = 'SomeAttribute'