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 the emp_subsidiary table. The query shows an attached_subqueries with a nested loop from the Employee table to the Department table.

MySQL UPDATE

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.

MySQL UPDATE

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”.

MySQL UPDATE

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.

MySQL UPDATE
%d bloggers like this: