Optimizing a query for optional fields from another table

Question

I have a innodb table called items that powers one ecommerce site. The search system allows you to search for optional/additional fields, so that you can e.g. search for only repaired computers or cars only older than 2000.

This is done via additional table called items_fields. It has a very simple design:

+------------+------------------------+------+-----+---------+----------------+
| id         | int(11)                | NO   | PRI | NULL    | auto_increment |
| field_id   | int(11)                | NO   | MUL | NULL    |                |
| item_id    | int(11)                | NO   | MUL | NULL    |                |
| valueText  | varchar(500)           | YES  |     | NULL    |                |
| valueInt   | decimal(10,1) unsigned | YES  |     | NULL    |                |
+------------+------------------------+------+-----+---------+----------------+

There is also a table called fields which contains only field names and types.

The main query, which returns search results, is the following:

SELECT items...   
FROM items   
WHERE items... AND (  
      SELECT count(id)  
      FROM items_fields    
      WHERE items_fields.field_id = "59" AND items_fields.item_id = items.id AND
      items_fields.valueText = "Damaged")>0  
ORDER by ordering desc LIMIT 35;

On a large scale (4 million+ search queries only, per day), I need to optimize these advanced search even more. Currently, the average advanced search query takes around 100ms.

How can I speed up this query? Do you have any other suggestions, advices, for optimization? Both tables are innodb, server stack is absolutely awesome, however I still got this query to solve :)

Answer 1

Add and index for (item_id, field_id, valueText) since this is your search.

Get rid of the inner select!!! MySQL up to 5.5 cannot optimize queries with inner selects. As far as I know MariaDB 5.5 is the only MySQL replacement that currently supports inner select optimization.

 SELECT i.*, f2.* as damageCounter FROM items i  
    JOIN items_fields f ON f.field_id = 59
                       AND f.item_id = i.id
                       AND f.valueText = "Damaged"
   JOIN item_fields f2 ON f2.item_id = i.id
   ORDER by i.ordering desc 

   LIMIT 35;

The first join will limit the set being returned. The second join will grab all item_fields for items meeting the first join. Between the first and last joins, you can add more Join conditionals that will filter out results based on additional points. For example:

   SELECT i.*, f3.* as damageCounter FROM items i  
    JOIN items_fields f ON f.field_id = 59
                       AND f.item_id = i.id
                       AND f.valueText = "Damaged"
   JOIN items_fields f2 ON f2.field_id = 22
                       AND f2.item_id = i.id
                       AND f.valueText = "Green"
   JOIN item_fields f3 ON f3.item_id = i.id
   ORDER by i.ordering desc 

   LIMIT 35;

This would return a result set of all items that had fields 59 with the value of "Damaged" and field 22 with the value of "Green" along with all their item_fields.