MySQL not using indexes; using filesort

Question

MySQL appears to be not using indexes and is using filesort on the following query:

  SELECT `tweets`.* 
    FROM `tweets` 
   WHERE (`tweets`.contest_id = 159) 
ORDER BY tweet_id ASC, tweeted_at DESC LIMIT 100 OFFSET 0

I have indexes on contest_id, tweet_id and tweeted_at

When I execute EXPLAIN EXTENDED, Extra returns "Using where; using filesort". How can I improve my query?

Answer 1

When you mix ASC and DESC sorting, MySQL cannot use indexes to optimize the GROUP BY statement.

Also, using multiple keys to sort will result in it not being able to optimize the query with indexes.

From the docs:

http://dev.mysql.com/doc/refman/5.6/en/order-by-optimization.html

In some cases, MySQL cannot use indexes to resolve the ORDER BY, although it still uses indexes to find the rows that match the WHERE clause. These cases include the following:

You use ORDER BY on different keys:

SELECT * FROM t1 ORDER BY key1, key2;

...

You mix ASC and DESC:

SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 ASC;

If the two columns you are ordering on are not part of the same key, then you are doing both of the above things.