mysql: Optimizing query w/ mixed-ascendency ORDER BY
I have a large table (~1M rows now, soon ~10M) that has two ranked columns (in addition to the regular data):
-
开发者_JAVA技巧
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?
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
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.
精彩评论