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
in (SELECT EMP_ID FROM EMPLOYEE,DEPARTMENT

WHERE DPT_ID = EMP_DEPT
AND DPT_AVG_SALARY<10000)​

Here the following is the query plan of this SQL, it takes 17.86 seconds to update emp_subsidiary table. The query shows an attached_subqueries with a nested loop from Employee table to 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.

https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/