查询中同时使用 group by 和 order by 时避免创建临时表

发布于 2024-10-08 06:16:29 字数 222 浏览 6 评论 0原文

有什么办法可以避免在这样的查询中创建临时表吗？

SELECT item_id FROM titem
GROUP BY item_id
ORDER BY item_created;

我尝试创建索引 (item_id, item_created) 和 (item_created, item_id)，但这没有帮助。

原文

Is there any way to avoid creation of temporary table in such a query?

SELECT item_id FROM titem
GROUP BY item_id
ORDER BY item_created;

I tried to create indexes (item_id, item_created) and (item_created, item_id), but that didn't help.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

情域 2024-10-15 06:16:29

您的查询有问题。当执行分组依据时，为每个组选择哪个 item_created ？默认情况下，MySQL 会或多或少随机地（从组内）为每个组选择一个。由于每个组仅使用一个 item_created ，因此需要一个临时表对其进行排序。

我建议您使用聚合函数之一，例如 min() 或 max() 来获得更好定义的 item_created 进行排序。这仍然无法解决临时表问题，item_id 上的索引大约是您能做的最好的事情。

SELECT item_id, max(item_created) as ic FROM titem
GROUP BY item_id
ORDER BY ic;

有趣的是，一些数据库，例如 Oracle（据我所知）会在原始查询中抛出错误，因为 item_created 既不在聚合函数的选定字段中，也不在 group by 子句中。 MySQL 可以设置为模仿这种行为。

You have a problem with your query. When the group by is performed which item_created is selected for each group? MySQL by default will select one for each group more or less randomly (from within the group). As only one item_created is used for each group a temporary table is required to sort it.

I would advise you to use one of the aggregation functions such as min() or max() to get a abetter defined item_created to sort on. This still won't fix teh temporary table problem, an index on item_id is about the best you can do.

SELECT item_id, max(item_created) as ic FROM titem
GROUP BY item_id
ORDER BY ic;

It is interesting to note that some databases eg Oracle (i'm told) would throw and error on your original query as item_created is neither in the selected fields in an aggregate function nor is it in the group by clause. MySQL can be set to mimic this behaviour.

回复收藏 0 原文

~没有更多了~