title: Multiple left joins and performance
body: <p>I have following tables:</p>
  
  <p><strong>products - 4500 records</strong></p>
  
  <p>Fields: id, sku, name, alias, price, special_price, quantity, desc, photo, manufacturer_id, model_id, hits, publishing </p>
  
  <p><strong>products_attribute_rel - 35000 records</strong></p>
  
  <p>Fields: id, product_id, attribute_id, attribute_val_id</p>
  
  <p><strong>attribute_values - 243 records</strong></p>
  
  <p>Fields: id, attr_id, value, ordering</p>
  
  <p><strong>manufacturers - 29 records</strong></p>
  
  <p>Fields: id, title,publishing</p>
  
  <p><strong>models -  946 records</strong></p>
  
  <p>Fields: id, manufacturer_id, title, publishing</p>
  
  <p>So I get data from these tables by one query:</p>
  
  <pre><code>SELECT jp.*,
         jm.id AS jm_id,
         jm.title AS jm_title,
         jmo.id AS jmo_id,
         jmo.title AS jmo_title
  FROM `products` AS jp 
  LEFT JOIN `products_attribute_rel` AS jpar ON jpar.product_id = jp.id
  LEFT JOIN `attribute_values` AS jav ON jav.attr_id = jpar.attribute_val_id
  LEFT JOIN `manufacturers` AS jm ON jm.id = jp.manufacturer_id
  LEFT JOIN `models` AS jmo ON jmo.id = jp.model_id 
  GROUP BY jp.id HAVING COUNT(DISTINCT jpar.attribute_val_id) &gt;= 0 
  </code></pre>
  
  <p>This query is slow as hell. It takes hundreds of seconds mysql to handle it.
  So how it would be possible to improve this query ? With small data chunks it works 
  perfectly well. But I guess everything ruins <code>products_attribute_rel</code> table, which
  has 35000 records. </p>
  
  <p>Your help would be appreciated. </p>
  
  <p><strong>EDITED</strong></p>
  
  <p><code>EXPLAIN</code> results of the <code>SELECT</code> query: </p>
  
  <p><img src="https://i.stack.imgur.com/m4Sgo.jpg" alt="EXPLAIN results of the SELECT query"></p>