title: Debugging Slow mySQL query with Explain
body: <p>Have found an inefficient query in our system. <code>content</code> holds versions of slides, and this is supposed to select the highest version of a slide by id.</p>
  
  <pre><code>SELECT `content`.*
  FROM (`content`)
  JOIN (
  SELECT max(version) as `version` from `content`
  WHERE `slide_id` = '16901'
  group by `slide_id`
  ) c ON `c`.`version` = `content`.`version`;
  </code></pre>
  
  <p>EXPLAIN</p>
  
  <pre><code>+----+-------------+------------------+------------+--------+--------------------------------------------------------------------------------+------------------------------------+---------+-------+------+----------+--------------------------+
  | id | select_type | table            | partitions | type   | possible_keys                                                                  | key                                | key_len | ref   | rows | filtered | Extra                    |
  +----+-------------+------------------+------------+--------+--------------------------------------------------------------------------------+------------------------------------+---------+-------+------+----------+--------------------------+
  |  1 | PRIMARY     | &lt;derived2&gt;       | NULL       | system | NULL                                                                           | NULL                               | NULL    | NULL  |    1 |   100.00 | NULL                     |
  |  1 | PRIMARY     | content          | NULL       | ref    | PRIMARY,version                                                                | PRIMARY                            | 8       | const | 9703 |   100.00 | NULL                     |
  |  2 | DERIVED     | content          | NULL       | ref    | PRIMARY,fk_content_slides_idx,thumbnail_asset_id,version,slide_id                      | fk_content_slides_idx              | 8       | const |    1 |   100.00 | Using where; Using index |
  +----+-------------+------------------+------------+--------+--------------------------------------------------------------------------------+------------------------------------+---------+-------+------+----------+--------------------------+
  </code></pre>
  
  <p>One big issue is that it returns almost all the slides in the system as the outer query does not filter by slide id.  After adding that I get...</p>
  
  <pre><code>SELECT `content`.* 
  FROM (`content`) 
  JOIN ( 
  SELECT max(version) as `version` from `content`  
  WHERE `slide_id` = '16901' group by `slide_id` 
  ) c ON `c`.`version` = `content`.`version` 
  WHERE `slide_id` = '16901';
  </code></pre>
  
  <p>EXPLAIN</p>
  
  <pre><code>+----+-------------+------------------+------------+--------+--------------------------------------------------------------------------------+------------------------------------+---------+-------------+------+----------+--------------------------+
  | id | select_type | table            | partitions | type   | possible_keys                                                                  | key                                | key_len | ref         | rows | filtered | Extra                    |
  +----+-------------+------------------+------------+--------+--------------------------------------------------------------------------------+------------------------------------+---------+-------------+------+----------+--------------------------+
  |  1 | PRIMARY     | &lt;derived2&gt;       | NULL       | system | NULL                                                                           | NULL                               | NULL    | NULL        |    1 |   100.00 | NULL                     |
  |  1 | PRIMARY     | content          | NULL       | const  | PRIMARY,fk_content_slides_idx,version,slide_id                                 | PRIMARY                            | 16      | const,const |    1 |   100.00 | NULL                     |
  |  2 | DERIVED     | content          | NULL       | ref    | PRIMARY,fk_content_slides_idx,thumbnail_asset_id,version,slide_id              | fk_content_slides_idx              | 8       | const       |    1 |   100.00 | Using where; Using index |
  +----+-------------+------------------+------------+--------+--------------------------------------------------------------------------------+------------------------------------+---------+-------------+------+----------+--------------------------+
  </code></pre>
  
  <p>That reduces the amount of rows down to one correctly, but doesnt really speed things up.</p>
  
  <p>There are indexes on version, slide_id and a unique key on version AND slide_id.</p>
  
  <p>Is there anything else I can do to speed this up?   </p>
  
  <p>Use a TOP LIMIT 1 insetead of Max ?</p>
  
  <p>m</p>