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

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:
    31
    Likes Received:
    14
    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