How to Tune “Not Exists” SQL statement?

Discussion in 'MySQL' started by Richard To, Sep 29, 2020.

  1. Richard To

    Richard To Member

    Sep 18, 2017
    Likes Received:
    Trophy Points:
    The following is an example shows a SQL statement with “Not Exists” expression. The SQL retrieve records from emp_subsidiary that satisfy with the “Not Exists” subquery.

    select * from emp_subsidiary sub
    where not (exists
    (select 'x' from employee emp
    where emp.emp_salary<1000
    and emp.emp_dept=sub.emp_dept ))​
    and sub.emp_grade<1200

    Here the following is the query plan of this SQL, it takes 13.36 seconds to finish. The query shows a Nested Loop from emp_subsidiary to the “Materialized Subquery2” from a full table scan of employee.
    I found the Rows=2950038 of “Full Table Scan of employee” of step 2 is significantly high to constitute the materialized subquery2(view). In order to reduce the actual number of rows scan for this materialized subquery2(view). I moved the subquery of “Not Exists” to a CTE “WITH” statement and added a “group by 1” to reduce the result set of the CTE in the following.

    with DT1
    as (select emp.emp_dept
    from employee emp
    where emp.emp_salary < 1000
    group by 1)​
    select * from emp_subsidiary sub
    where not (sub.emp_dept in (select emp_dept from DT1) )
    and sub.emp_grade < 1200​

    The following is the query plan of the rewritten SQL and it takes only 2.32 seconds to complete. The new query plan shows an “Index Range Scan” to the Employee table plus a “GROUP” operation to narrow down the result set of the materialized subquery2.
    This kind of rewrites can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is more than 5 times faster than the original SQL. There are some other rewrites shown in this screen with even better performance, but they are more complicated in SQL syntax transformation and it is not suitable to discuss here in this short article.
    shabbir 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