MySQL 5: It doesn't matter in what order my GROUP BY fields are?

Question

hevy

I have several aggregated / computed fields in my MySQL query. The GROUP BY clause is dynamically generated depending on what parameters the user selects in the web form. It is curious if the order of the fields listed in the GROUP BY clause can have any effect on the calculations (things like SUM, MEANS, etc.)

Thank!

Answer 1

It matters if you use WITH ROLLUP , otherwise it should not be.

Answer 2

Actually, I just tried this for the problem I had, and it turns out that it is very important to use the index.

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

SELECT COUNT (*) FROM Customer GROUP BY City, EXPLAIN shows that it uses an index.

But...

SELECT COUNT (*) FROM Customer GROUP BY State, City EXPLAIN indicates that it is not using an index.

This is on MySQL 5.1 with an InnoDB table.

Answer 3

no, that doesn't matter.