I have a large table (~1M rows now, soon ~10M) that has two ranked columns (in addition to the regular data):
avg_visited
, a float 0-1 representing a %age popularity; higher is betteralexa_rank
, an integer 1-N giving an a priori ranking
The a priori ranking is from external sources so can't be changed. Many rows have no popularity yet (as no user has yet hit it), so the a priori ranking is the fallback ordering. The popularity however does change very frequently - both to update old entries and to add a popularity to ones that previously only had the a priori ranking, if some user actually hits it.
I frequently run SELECT id, url, alexa_rank, avg_visited FROM
sitesORDER BY avg_visited desc, alexa_rank asc LIMIT 49500, 500
(for various values of 49500).
However, ORDER BY cannot use an index with mixed ascendency per http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html
This is in mysql 5.1, innodb.
How can I best change this situation to give me a sane, fully indexed query?