MySQL - slow page load after adding multiple ORDER BY

For some reason, after adding 2 more ORDER BY, my query became very slow. Can someone help me to fix it? I really need to use the following ORDER BY clause:

ORDER BY candidates.user_id DESC, candidates.usr_type ASC, all_us开发者_JS百科ers.user_id DESC

The main problem is that you are mixing sort orders, this is very slow in MySQL.
Either ORDER BY everything ASC or DESC, but don't mix them.

One solution is to define an extra copy field for usr_type that runs in the opposite order. Like this

Example
-------------------------------
id     usr_type    alt_usr_type

1      1           99
2      2           98
3      1           99
4      5           95

Now you can define the select as

ORDER BY candidates.user_id DESC
       , candidates.alt_usr_type DESC
       , all_users.user_id DESC

And your query will run much faster.

See: http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html

Make sure you have indexes on all fields you're ordering by.