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