title: MySQL query index
body: <p>I am using MySQL 5.6 and try to optimize next query:</p>
  <pre><code>SELECT t1.field1,
         ...
         t1.field30,
         t2.field1
  FROM Table1 t1
           JOIN Table2 t2 ON t1.fk_int = t2.pk_int
  WHERE t1.int_field = ?
    AND t1.enum_filed != 'value'
  ORDER BY t1.created_datetime desc;
  </code></pre>
  <p>A response can contain millions of records and every row consists of 31 columns.</p>
  <p>Now EXPLAIN says in Extra that planner uses 'Using where'.</p>
  <p>I tried to add next index:</p>
  <pre><code>create index test_idx ON Table1 (int_field, enum_filed, created_datetime, fk_int);
  </code></pre>
  <p>After that EXPLAIN says in Extra that planner uses &quot;Using index condition; Using filesort&quot;</p>
  <p>&quot;rows&quot; value from EXPLAIN with index is less than without it. But in practice time of execution is longer.</p>
  <p>So, the questions are next:</p>
  <ol>
  <li>What is the best index for this query?</li>
  <li>Why EXPLAIN says that 'key_len' of query with index is 5. Shouldn't it be 4+1+8+4=17?</li>
  <li>Should the fields from ORDER BY be in index?</li>
  <li>Should the fields from JOIN be in index?</li>
  </ol>