The following is an example shows a SQL statement with two union operator in a subquery. The SQL retrieve records from EMPLOYEE table that EMP_ID should satisfy with the union result set from two queries in a subquery. select * from employee where emp_id IN (select emp_id from emp_subsidiary where emp_grade=1000 union select emp_id from employee where emp_dept='AAA') Here the following are the query plans in Tosska proprietary tree format, it takes 3 minutes 27 seconds to finish. The query plan shows a full table scan of EMPLOYEE table and the attached subquery will be executed for each of scanned record. So, you can see the query plan is very inefficient. If we know the union result set is small and it should be executed first and use EMP_ID index to retrieve EMPLOYEE table. Let me rewrite the Union subquery as a derived table expression in the following: select * from employee where emp_id in (select emp_id from (select emp_id from emp_subsidiary where emp_grade = 1000 union select emp_id from employee where emp_dept = 'AAA') DT1) Now, you can see the Union subquery is executed first and use it to retrieve the EMPLOYEE table by EMP_ID index. The overall query is now become more reasonable and efficient. This kind of rewrites can be achieved by Tosska SQL Tuning Expert for MySQL automatically, it shows that the rewrite is more than 60 times faster than the original SQL. There are some other rewrites with even better performance, but it is a little bit complicated to discuss in this short article, let’s discuss it in my coming blogs. https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/
