How to Improve Query Performance with many JOINs

Question

I have a query (with the purpose of making a view) which is using a few joins to get each column. Performance degrades quickly (exponentially?) for each set of joins added.

What would be a good approach to make this query faster? Please see comments within the query.

If it helps, this is using the WordPress DB schema.

Here is a screenshot of EXPLAIN

PRODUCTS TABLE

+--+----+ |id|name| +--+----+ |1 |test| +--+----+ 

METADATA TABLE

+----------+--------+-----+ |product_id|meta_key|value| +----------+--------+-----+ |1         |price   |9.99 | +----------+--------+-----+ |1         |sku     |ABC  | +----------+--------+-----+ 

TERM_RELATIONSHIPS TABLE

+---------+----------------+ |object_id|term_taxonomy_id| +---------+----------------+ |1        |1               | +---------+----------------+ |1        |2               | +---------+----------------+ 

TERM_TAXONOMY TABLE

+----------------+-------+--------+ |term_taxonomy_id|term_id|taxonomy| +----------------+-------+--------+ |1               |1      |size    | +----------------+-------+--------+ |2               |2      |stock   | +----------------+-------+--------+ 

TERMS TABLE

+-------+-----+ |term_id|name | +-------+-----+ |1      |500mg| +-------+-----+ |2      |10   | +-------+-----+ 

QUERY

SELECT    products.id,   products.name,   price.value AS price,   sku.value AS sku,   size.name AS size FROM products  /* These joins are performing quickly */  INNER JOIN `metadata` AS price ON products.id = price.product_id AND price.meta_key = 'price' INNER JOIN `metadata` AS sku ON products.id = sku.product_id AND sku.meta_key = 'sku'  /* Here's the part that is really slowing it down - I run this chunk about 5 times with different strings to match */  INNER JOIN `term_relationships` AS tr ON products.id = tr.object_id   INNER JOIN `term_taxonomy` AS tt   ON tr.term_taxonomy_id = tt.term_taxonomy_id AND tt.taxonomy = 'size'     INNER JOIN `terms` AS size     ON tt.term_id = size.term_id 

Answer 1

Your performance issue is most likely caused by the join with the 'term_taxonomy' table.
All other joins seems to use the primary key (where you probobly have working indexes on).

So my suggestion is to add a compound index on term_taxonomy_id and term_id (or if you must: taxonomy). Like this:

CREATE UNIQUE INDEX idx_term_taxonomy_id_taxonomy ON term_taxonomy( term_taxonomy_id, taxonomy); 

Hope this will help you.