I have a query in which I am ordering a league table by a number of fields to handle the situation that some fields in the result may have the same value. I am curious as to why when subsequent fields aren't required for secondary ordering the query is still conciderably slower.
Case in point is a table of items that are voted on.
It contans a total
field which is a cached value of votes_up
minus votes_down
. When querying a leaderboard (say top 10) if two totals are equal it then orders by votes_up
. And finally if both are equal some other field could be used such as the primary key.
Here's an example table:
CREATE TABLE `items` (
`id` int unsigned NOT NULL,
`votes_up` mediumint NOT NULL,
`votes_down` mediumint NOT NULL,
`total` mediumint NOT NULL,
PRIMARY KEY (`id`),
KEY `votes_up` (`votes_up`),
KEY `total` (`total`)
)
A basic top-ten query with ordering on one field is lightning fast; e.g:
SELECT * FROM `items` ORDER BY `total` DESC LIMIT 10;
(0.00 secs)
Adding the votes_up field for secondary ordering slows it down considerably; e.g:
SELECT * FROM `items` ORDER BY `total` DESC, `votes_up` DESC LIMIT 10;
(0.15 secs)
Adding a third, even the primary key slows it down further; e.g.:
SELECT * FROM `items` ORDER BY `total` DESC, `votes_up` DESC, `id` DESC LIMI开发者_如何学CT 10;
(0.18 secs)
Is there any way to optimize this query so that when the total
field values are all unique that the secondary ordering clauses are ignored and don't incur so much overhead?
A multi-column index might speed this up. You can create an index on 'total' and 'votes_up'. Adding the 'id' will do nothing since that will always be unique.
With a multi-column index, always keep them in the same order that you are using for your ordering.
You can select your results into a temporary table, and from there, determine the uniqueness of the 'total' column and manage any subordering accordingly. Alternately, you could put your primary query (order by 'total') into a View, and only apply secondary ordering on that; that should speed it up somewhat.
精彩评论