title: optimize mysql join query using indexes
body: <p>I have the index <code>complex1(field2,field3)</code> for searching in <code>table1</code> and <code>index order1(field10)</code> for ordering the results. The <code>table1</code> is MySQL InndoDB table. By sub-query I try to obtain values using <code>complex1</code> index, and by main query I want to order them using <code>order1</code> index and leave only first 50.</p>
  
  <pre><code>  EXPLAIN(
  SELECT `t1`.`field1`,`t1`.`field10` from `database`.`table1` `t1` INNER JOIN 
     (SELECT `field1` FROM `database`.`table1` WHERE 
     `field2` &gt;= 'val1' and `field2`&lt;='val2' `and `field3` &gt;= 'val3' and `field3`&lt;='val4'
     ) as e1 
  ON e1.`field1`=t1.`field1` ORDER BY t1.`field10` LIMIT 50)
  </code></pre>
  
  <p>The results are following:</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    13  "Using temporary; Using filesort"
  1   PRIMARY t1  eq_ref  PRIMARY PRIMARY 4   e1.field1   1   
  2   DERIVED table1  range   complex1    complex1    8   NULL    13  "Using where; Using index"
  </code></pre>
  
  <p>As I see <code>complex1</code> is used and <code>order1</code> doesn't. Why?</p>