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 FROMsites
ORDER 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?
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
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.