1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

How to Tune SQL with “Group By” statement?

Discussion in 'MySQL' started by Richard To, Nov 10, 2020.

  1. Richard To

    Richard To Member

    Joined:
    Sep 18, 2017
    Messages:
    39
    Likes Received:
    17
    Trophy Points:
    8
    Gender:
    Male
    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.

    [​IMG]
    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.
    [​IMG]
    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.
    [​IMG]
    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/
    [​IMG]
     
    shabbir likes this.

Share This Page