title: how to make mysql derived table use index
body: <p>MySQL version 5.6.33</p>
  
  <p>I have two tables (<strong>files</strong> &amp; <strong>details</strong>) to inner join </p>
  
  <p>table <strong>files</strong>, has an index called <strong>idx_record</strong>(record_id)</p>
  
  <p>table <strong>details</strong>, has an index called <strong>idx_end_org_record</strong>(end_at, org_id, record_id)
  </p>
  
  <pre><code>explain
  SELECT
      id as file_id,
      record_id,
      recording_path
  FROM `files`
  INNER JOIN
  (
      SELECT `details`.`record_id`
      FROM `details` 
      WHERE `details`.`org_id` IN (6231, 6232, 6233, 6234) 
      AND (`details`.`end_at` BETWEEN '2017-05-31 16:00:00' AND '2017-06-13 16:00:00') 
      ORDER BY end_at desc
  ) as b
      on `files`.record_id = b.record_id 
  WHERE (file_name IS NOT NULL )
  LIMIT 30
  </code></pre>
  
  <p>Output as below</p>
  
  <pre><code>+----+-------------+--------------+-------+--------------------+--------------------+---------+------------------+---------+--------------------------+
  | id | select_type | table        | type  | possible_keys      | key                | key_len | ref              | rows    | Extra                    |
  +----+-------------+--------------+-------+--------------------+--------------------+---------+------------------+---------+--------------------------+
  |  1 | PRIMARY     | &lt;derived2&gt;   | ALL   | NULL               | NULL               | NULL    | NULL             | 3844632 | Using where              |
  |  1 | PRIMARY     | files        | ref   | idx_record         | idx_record         | 5       | b.record_id      |       1 | Using where              |
  |  2 | DERIVED     | details      | range | idx_end_org_record | idx_end_org_record | 11      | NULL             | 3844632 | Using where; Using index |
  +----+-------------+--------------+-------+--------------------+--------------------+---------+------------------+---------+--------------------------+
  </code></pre>
  
  <p>As you can see, the <code>&lt;derived2&gt;</code> table is not using any index, making this query take almost 1 second to finish.</p>
  
  <p>Any help is appreciated!</p>
  
  <hr>
  
  <p>Besides, removing <code>WHERE (file_name IS NOT NULL )</code> in the query doesn't make any difference.</p>
  
  <p>And in mysql 5.7, this problem doesn't even exist, but currently I'm trying to solve this in 5.6.</p>