Increasing the speed of a query with a sub select?

Question

I'm have a question. The following query is taking upwards of 2 - 3 seconds to exicute and I'm not sure why. I have 2 tables involved one with a list of items and the another with a list of attribute's for each item. The items table is indexed with unique primary key and the attributes table has a foreign key constraint.

The relationship between the items table is ONE TO MANY to the attributes.

I am not sure how else to speed up query and would appreciate any advice.

The database is MYSQL inodb

EXPLAIN SELECT * FROM eshop_items AS ite WHERE (SELECT attValue FROM eshop_items_attributes WHERE attItemId=ite.ItemId ANd attType=5 AND attValue='20')='20' ORDER BY itemAdded DESC LIMIT 0, 18;

id | select_type        | table                  | type | possible_keys | key       | key_len | ref                    | rows  | Extra
1   PRIMARY               ite                       ALL         NULL        NULL        NULL    NULL                     57179   Using where; Using   filesort
2   DEPENDENT SUBQUERY  eshop_items_attributes      ref     attItemId       attItemId    9      gabriel_new.ite.itemId      5    Using where

Index: eshop_items_attributes

Name        Fieldnames  Index Type  Index method
attItemId   attItemId   Normal      BTREE
attType     attType     Normal      BTREE
attValue    attValue    Normal      BTREE

Index: eshop_items

Name            Fieldnames      Index Type  Index method
itemCode        itemCode        Unique      BTREE
itemCodeOrig    itemCodeOrig    Unique      BTREE
itemConfig      itemConfig      Normal      BTREE
itemStatus      itemStatus      Normal      BTREE

Can't use a join because the item_attributes table is a key -> value pair table. So for every record in the items_attributes table there can be many item id's

here is a sample

item_id    attribute_index   attribute_value
12345      10                true
12345      2                 somevalue
12345      6                 some other value
32456      10                true
32456      11                another value
32456      2                 somevalue

So a join wouldn't work because I can't join multiple rows from the items_attributes table to one row in the items table.

I can't write a query where attribute_index is = to 2 AN attribute_index = 10. I would always get back no results.

:(

Answer 1

Change the query from correlated to IN and see what happens.

SELECT * 
  FROM eshop_items AS ite 
 WHERE ItemId IN (
       SELECT attItemId
         FROM eshop_items_attributes 
        WHERE attType=5 
          AND attValue='20')
 ORDER BY itemAdded DESC 
 LIMIT 0, 18 

You'll see further gains by changing your btree to bitmap on eshop_items_attributes. But be warned: bitmap has consequences on INSERT/UPDATE.