[MySQL optimization] MySQL group by Operation Optimization

Optimization of MySQL group by operations
BY default, MySQL performs the group by col1, col2.... operation according to the order of the group by field. If an order by clause containing the same columns is explicitly included, the actual execution performance of MySQL is not affected.

If the query includes the group by operation, but you do not need to sort the results, or you are not satisfied with the default sorting result, when you want to further process the result after obtaining it, order by null can be specified to prohibit sorting, so as to avoid consumption of sorting results.

The following example compares the execution plans that enable or disable group by sorting:

Mysql> desc select dep, pos, avg (sal) from employee group by dep, pos G

* *************************** 1. row ***************************

Id: 1

Select_type: SIMPLE

Table: employee

Type: ALL

Possible_keys: NULL

Key: NULL

Key_len: NULL

Ref: NULL

Rows: 10

Extra: Using temporary; Using filesort

1 row in set (0.00 sec)

Mysql> desc select dep, pos, avg (sal) from employee group by dep, pos order by null G

* *************************** 1. row ***************************

Id: 1

Select_type: SIMPLE

Table: employee

Type: ALL

Possible_keys: NULL

Key: NULL

Key_len: NULL

Ref: NULL

Rows: 10

Extra: Using temporary

1 row in set (0.00 sec)

From the execution plan, we can see that the SQL statement using ORDER BY NULL reduces the File Sorting steps. When the returned result set is large, the performance of GROUP BY is greatly improved.