ORDER BY NULL 比 ORDER BY 列慢
根据 MySQL 文档，添加 ORDER BY NULL GROUP BY 之后应该“避免对结果进行排序的开销”。 慢大约 5 倍
SELECT COUNT(*) advert_count, category.name
FROM advert
LEFT JOIN category ON advert.category_id = category.category_id
WHERE (
advert.state_id = 2
)
GROUP BY advert.category_id
ORDER BY NULL
LIMIT 5
如果是这样，为什么下面的查询比使用 ORDER BY advert_count 的查询
SELECT COUNT(*) advert_count, category.name
FROM advert
LEFT JOIN category ON advert.category_id = category.category_id
WHERE (
advert.state_id = 2
)
GROUP BY advert.category_id
ORDER BY advert_count DESC
LIMIT 5
？来自 phpMyAdmin 分析：
第一个查询：
Sorting for group -
Sorting result 0.000002
Sending data 12.069774
第二个查询：
Sorting for group 2.436986
Sorting result 0.000028
Sending data 0.000021
我对此感到困惑，有人能解释一下那里发生了什么吗？
according to MySQL documentation, adding ORDER BY NULL after GROUP BY should "avoid the overhead of sorting the result". If so, why is following query aprox 5 times slower
SELECT COUNT(*) advert_count, category.name
FROM advert
LEFT JOIN category ON advert.category_id = category.category_id
WHERE (
advert.state_id = 2
)
GROUP BY advert.category_id
ORDER BY NULL
LIMIT 5
than query with ORDER BY advert_count?
SELECT COUNT(*) advert_count, category.name
FROM advert
LEFT JOIN category ON advert.category_id = category.category_id
WHERE (
advert.state_id = 2
)
GROUP BY advert.category_id
ORDER BY advert_count DESC
LIMIT 5
From phpMyAdmin profiling:
1st query:
Sorting for group -
Sorting result 0.000002
Sending data 12.069774
2nd query:
Sorting for group 2.436986
Sorting result 0.000028
Sending data 0.000021
I am confused by this, could anyone explain me what is going on there?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
ORDER BY NULL根本不按任何顺序排序。每条记录都有相同的位置。因此第一个查询是选择数据中找到的前 5 个组。但第二个查询是计算数据中所有组的结果，并根据计数找到前 5 个组。
这是由您所展示的前两项管理费用证实的。
第三个开销是不同之处 - 发送数据。由于与数据库无关的原因，当您运行它时，需要很长时间才能传输数据。这可能是由于当时的服务器或网络负载造成的。
ORDER BY NULLis not ordering by anything at all. Every record gets the same position.So the first query is selecting the first 5 groups found in the data. But the second query is calculating the results for all groups in the data, and finding the top 5 based on the count.
This is born out by the first two overheads you have shown.
The 3rd overhead is the difference - Sending Data. For a non-database related reason, when you ran it it took a long time to transfer the data. This is likely due to server or network load at that moment in time.