How to Tune SQL Statement with Driving Path Control for MySQL?

Discussion in 'MySQL' started by Richard To, Jan 4, 2021.

  1. Richard To

    Richard To Member

    Joined:
    Sep 18, 2017
    Messages:
    63
    Likes Received:
    21
    Trophy Points:
    8
    Gender:
    Male
    The following is an example shows a SQL statement with two potential table join paths. “Employee to Department” and “Department to Employee” are potential driving paths which will be decided by MySQL SQL optimizer during SQL optimization stage.

    select emp_id,emp_name,dpt_avg_salary
    from employee ,department
    where emp_dept=dpt_id
    and emp_dept like 'A%'
    and dpt_id like 'A%';​

    Here the following is the query plan selected by MySQL SQL optimizer in Tosska proprietary tree format, it takes 59 seconds to finish.
    [​IMG]
    The query plan looks reasonable that uses DEPARTMENT’s Primary Key (DPT_ID) to fetch DEPARTMENT table first and then nested loop EMPLOYEE table by EMP_DEPT index, the speed of this query plan depends on the size of EMPLOYEE table and the records distribution according to the EMP_DEPT code.

    If we want to change the driving path of the query plan from EMPLOYEE to DEPARTMENT, let me add a ifnull(dpt_id,dpt_id) dummy function to disable the DPT_ID index search, so it can artificially add cost to condition search DEPARTMENT table first. It means that using EMP_DEPT index search cost is relative cheaper now, so EMPLOYEE to DEPARTMENT driving path is probably be selected by MySQL SQL optimizer in the following:

    select emp_id,
    emp_name,
    dpt_avg_salary​
    from employee,
    department​
    where emp_dept = dpt_id
    and emp_dept like 'A%'
    and ifnull(dpt_id,dpt_id) like 'A%'​

    EMPLOYEE to DEPARTMENT driving path plan is generated by MySQL now and it takes only 18.8 seconds only to finish the query.
    [​IMG]
    If we know that using the EMP_DEPT index is not that efficient due to the selectivity of “ like 'A%' “ condition may not high enough to utilize the index range scan. Let me add an additional ifnull(emp_dept,emp_dept) dummy function to disable the EMP_DEPT index range scan too in the following:

    select emp_id,
    emp_name,
    dpt_avg_salary​
    from employee,
    department​
    where emp_dept = dpt_id
    and ifnull(emp_dept,emp_dept) like 'A%'
    and ifnull(dpt_id,dpt_id) like 'A%'​
    [​IMG]
    Now, MySQL use full table scan of EMPLOYEE table to nested loop DEPARTMENT table. The speed is further improved to 15 seconds now.

    This kind of rewrites can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the best rewrite is around 4 times faster than the original SQL.
    https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/
    [​IMG]
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice