How to Tune UPDATE SQL statement with IN subquery (I) ?

Discussion in 'MySQL' started by Richard To, Oct 5, 2020.

  1. Richard To

    Richard To Member

    Joined:
    Sep 18, 2017
    Messages:
    52
    Likes Received:
    18
    Trophy Points:
    8
    Gender:
    Male
    The following is an example shows an Update SQL statement with an “IN” subquery. It updates records from emp_subsidiary that satisfies the “IN” subquery conditions.

    update emp_subsidiary set emp_name ='Deleted Name'
    where emp_dept in
    (select dpt_id from department
    where dpt_avg_salary<=6000);​

    Here the following is the query plan of this SQL, it takes 7.55 seconds to finish the update. The query shows an attached_subqueries attached to a Full Index Scan of emp_subsidiary table. It means that the 295344 rows in emp_subsidiary is going to check the subquery's conditions one by one.
    [​IMG]
    Let me rewrite the SQL into the following join update syntax.

    update emp_subsidiary e1, department d1
    set e1.emp_name='Deleted Name'​
    where e1.emp_dept = d1.dpt_id
    and d1.dpt_avg_salary <= 6000​

    The following is the query plan of the rewritten SQL and it takes only 1.22 seconds to complete. The new query plan shows a “Nested Loop” from Department table to Emp_subsidiary table, due to the condition “dpt_avg_salary <= 6000” has been executed before it is going to loop the Emp_subsidiary table, it saved a lot of unnecessary time to detect every record in Emp_subsidiary table.
    [​IMG]
    This kind of rewrites can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is more than 6 times faster than the original SQL.

    https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/
    [​IMG]
     
    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