MySQL 5: Does it matter what order my GROUP BY fields are in?

Question

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)

Thanks!

Answer 1

no, that shouldn't matter

Answer 2

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

Answer 3

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.

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

But...

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

This is on MySQL 5.1 with an InnoDB table.