The following is an example shows a DELETE SQL statement with subquery. The SQL delete records from emp_subsidiary that should satisfy with three conditions shows in the following query.
delete from emp_subsidiary
where emp_id in
(SELECT EMP_ID
FROM EMPLOYEE
WHERE emp_salary < 15000)
and emp_dept<‘D’
and emp_grade<1500
Here the following are the query plans in tabular format, it takes 8.88 seconds to finish.
Normally, DELETE SQL statements are difficult to tune due to the MySQL SQL optimizer generate a relative smaller plan space for DELETE statements compare to SELECT SQL statements. Simply speaking, there are not much alternative plans that MySQL will generate for you no matter how complicated SQL syntax you can rewrite for your DELETE statement. But there is a loophole in MySQL version 8, which we have to aware of is the order of conditions listed in the DELETE statement. The following rewrite which reordered the filtering conditions and has the same query plan as the original SQL both in Tree Plan and Tabular Plan. But the speed is improved to 3.88 seconds.
delete from emp_subsidiary
where emp_dept < ‘D’
and emp_grade < 1500
and emp_id in (select EMP_ID
from EMPLOYEE
where emp_salary < 15000)
Since there is no change in Tree Plan and Tabular Plan, we have to check the Visual Plan and found the following change in red box, it shows you that the Attached Condition’s execution order is changed and the time-consuming subquery is placed at the end of the Attached Condition. It means that either one of the first two conditions is false then the subquery is not necessary to execute. It is possibly can explain why the second DELETE statement is running much faster than the original SQL statement.
This kind of rewrites can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is more than 2 times faster than the original SQL.
https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/