How to Tune SQL Statement with Multiple Union in Subquery for MySQL?

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