I've got a large table (~1M rows now, soon ~10M) which has two rated posts (additionally towards the regular data):

  • avg_visited, a float -1 representing a %age recognition greater is much better
  • alexa_rank, an integer 1-N giving a b priori ranking

The a priori ranking comes from exterior sources so can not be transformed. Many rows don't have any recognition yet (as no user has yet hit it), therefore the a priori ranking may be the fallback ordering. The recognition however does change often - both to update old records and also to give a recognition to ones that formerly only had the a priori ranking, if some user really hits it.

I frequently run SELECT id, url, alexa_rank, avg_visited FROMsitesORDER BY avg_visited desc, alexa_rank asc LIMIT 49500, 500 (for a number of values of 49500).

However, ORDER BY cannot make use of an index with mixed ascendency per http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

This really is in mysql 5.1, innodb.

How do i best change this case to provide us a sane, fully indexed query?

Regrettably, MySQL doesn't support DESC clauses within the indexes, neither will it support indexes on derived expressions.

You are able to keep negative recognition together with the positive one and employ it within the ORDER BY:

CREATE INDEX ix_mytable_negpopularity_apriori ON (neg_popularity, a_priori);

INSERT
INTO    mytable (popularity, neg_popularity)
VALUES  (@popularity, -@popularity);

SELECT  *
FROM    mytable
ORDER BY
        neg_popularity, a_priori

Only a simple hack:

Since since recognition is really a float between to at least one. You are able to multiply it by -1 and also the number is going to be between -1 to .

By doing this you are able to turn back sort order of recognition to ORDER BY popularity ASC, a_priori ASC

Unsure the overhead out weighs in at the gain.

This jogs my memory from the hack of storing emails backwards form.