title: MySQL: Not using index for ORDER BY?
body: <p>I've been trying and googling everything and still can't figure out what's going on.</p>
  <p>I have a big table (100M+rows). Among others it has 3 columns: user_id, date, type.
  It has an index <code>idx(user_id, type, date)</code>.</p>
  <p>When I <code>EXPLAIN</code> this query:</p>
  <pre><code>  SELECT * 
      FROM table 
     WHERE user_id = 12345 
       AND type = 'X' 
  ORDER BY date DESC 
     LIMIT 5
  </code></pre>
  <p>EXPLAIN shows that MySQL examined 110K rows. which is roughly row many rows this user_id has.</p>
  <h3>My question is:</h3>
  <p>Why the same index is not used for <code>ORDER_BY LIMIT 5</code>? It knows which rows belong to the user_id, date is part of the same index, so why not just take last 5 rows in that index?</p>
  <p>P.S. I tried index by (user_id, date, type) - same results; i tried removing DESC - same results.</p>
  <p>This is the <code>EXPLAIN</code> plan:</p>
  <pre><code>  id: 1
    select_type: SIMPLE
    table: s
    type: ref
    possible_keys: dateIdx,userTypeDateIdx
    key: userTypeDateIdx
    key_len: 5
    ref: const,const
    rows: 110118
    Extra: Using where
  </code></pre>
  <p>I also tried adding <code>FORCE INDEX FOR ORDER BY</code> hint, but i still get <code>rows: 110118</code>.</p>