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.
精彩评论