mysql: Optimizing query w/ mixed-ascendency ORDER BY

Question

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 better
• alexa_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 FROMsitesORDER 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?

Answer 1

Unfortunately, MySQL does not support DESC clauses in the indexes, neither does it support indexes on derived expressions.

You can store the negative popularity along with the positive one and use it in 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

Answer 2

Just a simple hack:

Since since popularity is a float between 0 to 1. You can multiply it by -1 and the number will be between -1 to 0.

This way you can reverse the sort order of popularity to ORDER BY popularity ASC, a_priori ASC

Not sure the overhead out weighs the gain.

This reminds me of the hack of storing emails in reverse form.