CODEWITHSUNDEEP
mysqlsqldatabasegroup-byquery-performance
bjk116
bjk116

Reputation: 529

Why would a ending Group By without an aggregate slow down my query?

I recently came across in my inherited program a long SQL query that joined 8 tables and 3 views. Using EXPLAIN, I saw it was 7 Unique Key lookups and 4 non-unique key lookups. On average it took 18 seconds to fetch 350 rows (there's a number of reasons for that, each of those 3 views is made up of other views for one) Then, I noticed a GROUP BY tableone.id without any aggregate. I deleted that and now it runs in milliseconds.

Now the confusing part is that I then looked up why MySQL allows a GROUP BY statement without an aggregate function and learned it's actually an optimizing technique (Why does MySQL allow "group by" queries WITHOUT aggregate functions?).

This was obviously NOT the case in my situation. So why is that? When is a dangling GROUP BY a hindrance and not an optimizer?

Upvotes: 0

Views: 336

Answers (2)

GMB
GMB

Reputation: 222462

The GROUP BY clause, even without an actual aggregate function being used, implies additional processing for the RDBMS, in order to check if some records need to be aggregated. Thus the boost that you are seeing when removing an unnecessary GROUP BY.

The link you shared explains that this somehow loose behavior from MySQL might have been designed as a way to shorten the syntax of aggregate queries where grouping by one field would imply other fields are also being grouped, and possibly as an optimization as well. Anyway this does not properly fit your use case, where you don’t actually need aggregation.

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

The use of group by without aggregation function is not more allowed starting from mysql 5.6 (for obvious reasons.

For the versions previos then 5.7 the group by clause work extracting a (causal ) value for all the column not in aggregated function .. this others then an unpredictable result for these columns .. produce the need of work forscan all the rows and extract the result with a degradation of the performance .

Upvotes: 0

Related Questions