mySQL ORDER optimizing

Question

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 LIMIT 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?

Answer 1

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.

Answer 2

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.