sql - MySQL not using indexes; using filesort -

mysql appears not using indexes , using filesort on following query:

  select `tweets`.*      `tweets`     (`tweets`.contest_id = 159)  order tweet_id asc, tweeted_at desc limit 100 offset 0 

i have indexes on contest_id, tweet_id , tweeted_at

when execute explain extended, returns "using where; using filesort". how can improve query?

when mix asc , desc sorting, mysql cannot use indexes optimize group by statement.

also, using multiple keys sort result in not being able optimize query indexes.

from docs:

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

in cases, mysql cannot use indexes resolve order by, although still uses indexes find rows match clause. these cases include following:

you use order on different keys:

select * t1 order key1, key2;

...

you mix asc , desc:

select * t1 order key_part1 desc, key_part2 asc;

if 2 columns ordering on not part of same key, doing both of above things.