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