MySQL: Not using index for ORDER BY?

I've been trying and googling everything and still can't figure out what's going on.

I have a big table (100M+rows). Among others it has 3 columns: user_id, date, type. It has an index idx(user_id, type, date).

When I EXPLAIN this query:

  SELECT * 
    FROM table 
   WHERE user_id = 12345 
     AND type = 'X' 
ORDER BY date DESC 
   LIMIT 5

EXPLAIN shows that MySQL examined 110K rows. which is roughly row many rows this user_id has.

My question is:

Why the same index is not used for ORDER_BY LIMIT 5? 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.S. I tried index by (user_id, date, type) - same results; i tried removing DESC - same results.

This is the EXPLAIN plan:

  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

I also tried adding FORCE INDEX FOR ORDER BY hint, but i still get rows: 110118.开发者_如何转开发

Did you ANALYZE TABLE after creating the index?

Mysql will not use the index until the table is analyzed. The best index to use is the one you created with (user_id, type, date)

The date in the index is in ascending order, and you are asking for the most recent five rows in descending order by date; it can't use the index for that. If you changed the index to user_id, type, date desc it would be able to use the index to get the most recent five rows.