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 :)

Do you just want a list of all items that have a specific field in the item fields? Can you post an example of your desired result set? — Ray, Nov 13 '12 at 13:33
Also, will you always be having a query against the "Attributes" table of field/value pair you are looking for? Also, for the item_fields table, I would at a minimum have an index on (field_id, valueText ) and ( field_id, valueInt) — DRapp, Nov 13 '12 at 14:06

Ray · Answer 1 · 2012-11-13T14:01:07.413

0

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.

edited Nov 13 '12 at 14:01

answered Nov 13 '12 at 13:22

Ray

40,256
21
101
138

Yes, but I can also e.g. find all items that have field id of 59 and damaged record, then another field for let's say mileage with record "150000" (kilometres) and so on...so basically there are endless posibilities. In that case I need only to build more of 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 MySql server that I'm using is 5.5 percona, so no worries with Inner Join. – aez1 Nov 13 '12 at 13:31
@emirb Oh, I get it. You don't care about the count, you're just using it as true/false field. You may string together a number of ANDs. – Ray Nov 13 '12 at 13:37
@emirb see my update and let me know if it works for your situtaion – Ray Nov 13 '12 at 13:47
@emirb Also,you would stand to benefit greatly from an index (item_id, field_id, valueText) if that's what your searching on – Ray Nov 13 '12 at 14:00
I know, there's already and index that serves me really well :) Also on (item_id, field_id, valueInt). – aez1 Nov 13 '12 at 14:00
1

@emirb just make sure it's (item_id, field_id...) and not (field_id, item_id...). The order matters and I'm guessing the cardality of item_id is much higher than field_id – Ray Nov 13 '12 at 14:03

Optimizing a query for optional fields from another table

1 Answers1