title: MySQl Query: Join and Group By, slow performance
body: <p>I have a problem with a MySQL Query and I'm not able to optimize it.</p>
  
  <pre><code>SELECT 
    p.id, 
    p.name,
    p.sku, 
    p.type
  FROM 
    xm_products p 
    LEFT JOIN xm_store_product sp ON p.id = sp.product_id 
    LEFT JOIN xm_store s ON sp.store_id = s.id 
  WHERE 
    s.id = 1 
  ORDER BY 
    p.type, 
    p.name asc 
  LIMIT 
    20 OFFSET 0
  </code></pre>
  
  <p>This Query is very slow: Querytime 2.532s</p>
  
  <p>If I remove the Order By clause, the query is very fast: 0.0001s</p>
  
  <p>Explain show the following information:</p>
  
  <pre><code>+----+-------------+-------+--------+-------------------------------------------+----------------------+---------+---------------------------------------+--------+----------------------------------------------+
  | id | select_type | table | type   | possible_keys                             | key                  | key_len | ref                                   | rows   | Extra                                        |
  +----+-------------+-------+--------+-------------------------------------------+----------------------+---------+---------------------------------------+--------+----------------------------------------------+
  |  1 | SIMPLE      | s     | const  | PRIMARY                                   | PRIMARY              | 4       | const                                 |      1 | Using index; Using temporary; Using filesort |
  |  1 | SIMPLE      | sp    | ref    | IDX_CA42254AB092A811,IDX_CA42254A4584665A | IDX_CA42254AB092A811 | 5       | const                                 | 102157 | Using where                                  |
  |  1 | SIMPLE      | p     | eq_ref | PRIMARY                                   | PRIMARY              | 4       | model.sp.product_id                   |      1 |                                              |
  +----+-------------+-------+--------+-------------------------------------------+----------------------+---------+---------------------------------------+--------+----------------------------------------------+
  3 rows in set
  </code></pre>
  
  <p>I have 3 tables:</p>
  
  <ul>
  <li>xm_product with primary key id </li>
  <li>xm_store with primary key id</li>
  <li>xm_store_product with index on store_id and product_id</li>
  </ul>
  
  <p>I tried adding an index on p.name and p.type and also a combined index (p.name, p.type) but it did not help.</p>
  
  <p>How can I optimize the performance of this query?</p>
  
  <p>EDIT:</p>
  
  <p>I took me 2 hours to create a sqlfidle. But here it is with some data in it.
  <a href="http://sqlfiddle.com/#!2/2bf8d/1" rel="nofollow">http://sqlfiddle.com/#!2/2bf8d/1</a></p>
  
  <p>The problem is the "Group By" causing "Using index; Using temporary; Using filesort".</p>
  
  <p>How can I correct my example?</p>
  
  <p>EDIT 2:</p>
  
  <p>De xm_products table has about 200'000 records. The xm_store_products about 400'000. The Query is for a pager, the limit to 20 ist per page.</p>