How to Tune SQL statement with Transitive Dependency Improvement for MySQL?

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

  1. Richard To

    Richard To Member

    Sep 18, 2017
    Likes Received:
    Trophy Points:
    The following is an example shows a SQL statement with two conditions “emp_dept=dpt_id and emp_dept<'L'” .

    select * from employee, department
    where emp_dept=dpt_id
    and emp_dept<'L'
    and emp_id<1500000
    and emp_salary= dpt_avg_salary​
    order by dpt_avg_salary

    Here the following is the query plan of this SQL in Tosska proprietary tree format, it takes 8.84 seconds to finish.
    The query plan looks reasonable that shows a full table scan of DEPARTMENT to nested-loop EMPLOYEE table, the records in EMPLOYEE table being nested-loop must satisfy with the condition “emp_id<1500000” and the corresponding index EMPS_SALARY_INX is also used. Due to the number of records in the first driving table in a Nested Loop Join is very critical to the join performance, we should find a way to narrow down the number of result records of DEPARTMENT table before it is used to nested-loop EMPLOYEE table.

    As the conditions “emp_dept=dpt_id and emp_dept<'L'”, it implies that “dpt_id < 'L'” is also true, let me add this extra condition to the SQL, it helps MySQL SQL optimizer to make a better decision with more information provided by the new SQL syntax, this technique is especially useful for MySQL database.

    Oracle or MS SQL Server are doing very good on their internal Transitive Dependency Improvement in their SQL optimizer already, so this technique may not work for Oracle and MS SQL Server.

    select *
    from employee,department
    where emp_dept = dpt_id
    and dpt_id < 'L'
    and emp_dept < 'L'
    and emp_id < 1500000
    and emp_salary = dpt_avg_salary​
    order by dpt_avg_salary

    Let’s see the DEPARTMENT is now being filtered by the new condition “dpt_id < 'L' “ with an index range scan. You can see the estimated Rows 401 of DEPARTMENT table is now being trimmed down to 176. The rewritten SQL now takes only 3.8 seconds with such a simple change in syntax.
    This kind of rewrites can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that this rewrite is more than 2 times faster than the original SQL with such an easy change in the syntax.
    Sagar Jaybhay likes this.

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