title: How to Improve Query Performance with many JOINs
body: <p>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. </p>
  
  <p>What would be a good approach to make this query faster? Please see comments within the query.</p>
  
  <p>If it helps, this is using the WordPress DB schema.</p>
  
  <p>Here is a screenshot of EXPLAIN
  <img src="https://i.stack.imgur.com/2c7uz.png" alt="enter image description here"></p>
  
  <p><b>PRODUCTS TABLE</b></p>
  
  <pre><code>+--+----+
  |id|name|
  +--+----+
  |1 |test|
  +--+----+
  </code></pre>
  
  <p><b>METADATA TABLE</b></p>
  
  <pre><code>+----------+--------+-----+
  |product_id|meta_key|value|
  +----------+--------+-----+
  |1         |price   |9.99 |
  +----------+--------+-----+
  |1         |sku     |ABC  |
  +----------+--------+-----+
  </code></pre>
  
  <p><b>TERM_RELATIONSHIPS TABLE</b></p>
  
  <pre><code>+---------+----------------+
  |object_id|term_taxonomy_id|
  +---------+----------------+
  |1        |1               |
  +---------+----------------+
  |1        |2               |
  +---------+----------------+
  </code></pre>
  
  <p><b>TERM_TAXONOMY TABLE</b></p>
  
  <pre><code>+----------------+-------+--------+
  |term_taxonomy_id|term_id|taxonomy|
  +----------------+-------+--------+
  |1               |1      |size    |
  +----------------+-------+--------+
  |2               |2      |stock   |
  +----------------+-------+--------+
  </code></pre>
  
  <p><b>TERMS TABLE</b></p>
  
  <pre><code>+-------+-----+
  |term_id|name |
  +-------+-----+
  |1      |500mg|
  +-------+-----+
  |2      |10   |
  +-------+-----+
  </code></pre>
  
  <p><b>QUERY</b></p>
  
  <pre><code>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
  </code></pre>