MySQL does not use indexes; using filesort

Question

MySQL does not seem to use indexes and uses filesort for 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 .

In addition, using multiple keys for sorting will cause it to fail to optimize the query using 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 ORDER BY, although it still uses indexes to find strings 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 that you order are not part of the same key, then you do both of these things.