1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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

Discussion in 'MySQL' started by Richard To, Nov 30, 2020.

  1. Richard To

    Richard To Member

    Joined:
    Sep 18, 2017
    Messages:
    45
    Likes Received:
    17
    Trophy Points:
    8
    Gender:
    Male
    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.
    [​IMG]
    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)​
    [​IMG]
    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/

    [​IMG]
     

Share This Page