How to Tune SQL with “Group By” statement?

The following is an example shows a SQL statement with “Group by” expression. The SQL retrieve the sum of historical salary from table emp_sal_hist and they are current employees.

select sum(h.sal_salary),

e.emp_id​

from emp_sal_hist h,

employee e​

where e.emp_id < 1010000

and e.emp_id = h.sal_emp_id​

group by h.emp_id

Here the following are the query plans in tabular format and Tree format to show different information, it takes 3.13 seconds to finish.


The original SQL text with a “group by h.emp_id” clause which is used to group the h.emp_sal_hist’s emp_id for the summation of h.emp_sal_hist’s sal_salary. Let me change the “group by h.emp_id” to “group by e.emp_id” due the equal condition “e.emp_id = h.sal_emp_id”. Let me rewrite the SQL as the following:

select sum(h.sal_salary),

e.emp_id​

from emp_sal_hist h,

employee e​

where e.emp_id < 1010000

and e.emp_id = h.sal_emp_id​

group by e.emp_id


Here is the rewritten SQL’s tabular plan and there is no change in tree plan, a new MRR ("Disk-Sweep Multi-Range Read") is used to retrieve table emp_sal_hist.

This rewritten SQL takes 1.36 seconds to finish without significant change to the SQL text, you can base on your database’s data distribution of tables to try this optimization technique.

This kind of rewrites can also be achieved by Tosska SQL Tuning Expert for MySQL with Hints Injection optimization too, it shows that the Hints Injected SQL is more than 2 times faster than the original SQL.

https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/