mySQL ORDER optimizing

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.