MySQL 5：我的GROUP BY字段的顺序是否重要？

Peeps,

I have a few aggregate/calculated fields in my MySQL query. My GROUP BY clause is dynamically generated, depending on what options a user selects in a web form. Curious if the order of fields listed in the GROUP BY clause can have any impact on the calculations (things like SUMs, AVERAGEs, etc)

我的MySQL查询中有一些聚合/计算字段。我的GROUP BY子句是动态生成的,具体取决于用户在Web表单中选择的选项。好奇如果GROUP BY子句中列出的字段顺序会对计算产生任何影响(例如SUM,AVERAGE等)

Thanks!

3 个解决方案

#1

It WILL matter if you are using WITH ROLLUP, otherwise, it should not.

如果你使用WITH ROLLUP会很重要,否则它不应该。

#2

Actually, I just tried it for a problem I had, and it turns out it matters a great deal for index usage.

实际上,我只是尝试了解我遇到的问题,结果证明索引的使用非常重要。

Let's say we have a secondary index on a Customers table with two columns (City, State) -- and they are specified in this order in the index.

假设我们在Customers表上有一个二级索引,其中有两列(City,State) - 它们在索引中按此顺序指定。

SELECT COUNT(*) FROM Customers GROUP BY City, State EXPLAIN shows that it uses the index.

SELECT COUNT(*)FROM Customers GROUP BY City,State EXPLAIN显示它使用索引。

But...

SELECT COUNT(*) FROM Customers GROUP BY State, City EXPLAIN shows that it does not use the index.

SELECT COUNT(*)FROM Customers GROUP BY State,City EXPLAIN显示它不使用索引。

This is on MySQL 5.1 with an InnoDB table.

这是在带有InnoDB表的MySQL 5.1上。

#3

no, that shouldn't matter

不,那应该不重要

#1