How to Tune MySQL UPDATE SQL statement with IN subquery II?

I showed you how to tune an Update statement with a simple subquery in my last blog, a more complex update statement will be discussed in the following:

update emp_subsidiary set emp_name=concat(‘Sub ‘,emp_name)

where emp_id





Here the following is the query plan of this SQL, it takes 17.86 seconds to update the emp_subsidiary table. The query shows an attached_subqueries with a nested loop from the Employee table to the Department table.


Let me rewrite the SQL into the following join update syntax.

update emp_subsidiary e1, (select EMP_ID from EMPLOYEE, DEPARTMENT

where DPT_ID = EMP_DEPT and DPT_AVG_SALARY < 10000) DT1

set    e1.emp_name=concat(‘Sub ‘,e1.emp_name)

where  e1.emp_id = DT1.EMP_ID

This rewritten SQL takes 14.54 seconds and it is slightly faster than the original syntax with the following query plan. The query plan shows the attached_subqueries is replaced by a full table scan of EMP_SUBSIDIARY with two nested loops of EMPLOYEE and DEPARTMENT, this query plan is failed to use this condition “DPT_AVG_SALARY < 10000” to narrow down join data.


In order to control the driving path from EMP_SUBSIDIARY to EMPLOYEE and DEPARTMENT, I changed the select EMP_ID to ifnull(EMP_ID,EMP_ID) in the join subquery select item, it blocks the driving from EMP_SUBSIDIARY to EMPLOYEE by increasing the cost of nested loop EMPLOYEE without using the EMP_ID index.

update emp_subsidiary e1, (select ifnull(EMP_ID,EMP_ID) col1 from EMPLOYEE,

                           DEPARTMENT where DPT_ID = EMP_DEPT

                           and DPT_AVG_SALARY < 10000) DT1

set    e1.emp_name=concat(‘Sub ‘,e1.emp_name)

where  e1.emp_id = DT1.col1

The rewritten SQL now takes 1.26 seconds to finish the update and the step of the query plan is to full table scan of DEPARTMENT table with the condition “DPT_AVG_SALARY < 10000”.


This kind of rewrites can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is more than 14 times faster than the original SQL. Also, for simpler and more convenient query optimization, you can use MySQL developer tools.


Techdailytimes Editor

My passion for exploring technology and gathering unique information for the benefit of others has led me to pursue a career in news reporting. I take pride in providing timely coverage of the latest news across Pakistan as a personal hobby and professional responsibility."
Back to top button

Murtaza Ali

Typically replies within a day

%d bloggers like this: