开发者

MySQL - slow page load after adding multiple ORDER BY

开发者 https://www.devze.com 2023-04-06 01:12 出处:网络
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:

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.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号