The following is an example shows a SQL statement with “Not Exists” expression. The SQL retrieve records from emp_subsidiary that satisfy with the “Not Exists” subquery. select * from emp_subsidiary sub where not (exists (select 'x' from employee emp where emp.emp_salary<1000 and emp.emp_dept=sub.emp_dept ))and sub.emp_grade<1200 Here the following is the query plan of this SQL, it takes 13.36 seconds to finish. The query shows a Nested Loop from emp_subsidiary to the “Materialized Subquery2” from a full table scan of employee. I found the Rows=2950038 of “Full Table Scan of employee” of step 2 is significantly high to constitute the materialized subquery2(view). In order to reduce the actual number of rows scan for this materialized subquery2(view). I moved the subquery of “Not Exists” to a CTE “WITH” statement and added a “group by 1” to reduce the result set of the CTE in the following. with DT1 as (select emp.emp_dept from employee emp where emp.emp_salary < 1000 group by 1)select * from emp_subsidiary sub where not (sub.emp_dept in (select emp_dept from DT1) ) and sub.emp_grade < 1200 The following is the query plan of the rewritten SQL and it takes only 2.32 seconds to complete. The new query plan shows an “Index Range Scan” to the Employee table plus a “GROUP” operation to narrow down the result set of the materialized subquery2. This kind of rewrites can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is more than 5 times faster than the original SQL. There are some other rewrites shown in this screen with even better performance, but they are more complicated in SQL syntax transformation and it is not suitable to discuss here in this short article. https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/
