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

How to Tune CTE “WITH” SQL statement?

Discussion in 'MySQL' started by Richard To, Sep 21, 2020.

  1. Richard To

    Richard To Member

    Joined:
    Sep 18, 2017
    Messages:
    31
    Likes Received:
    14
    Trophy Points:
    8
    Gender:
    Male
    CTE stands for common table expression. A CTE allows you to define a temporary named result set that available temporarily in the execution scope of a statement such as SELECT, INSERT, UPDATE, DELETE...

    The following shows an example of a CTE in MySQL:
    WITH
    cte1 AS (SELECT a, b FROM table1),
    cte2 AS (SELECT c, d FROM table2)​
    SELECT b, d FROM cte1 JOIN cte2
    WHERE cte1.a = cte2.c;

    The following is an example shows a SQL statement with CTE WITH expression. The retrieve records from EMPLOYEE that EMP_GRADE and EMP_DEPT have to satisfy the CTE selection result.

    with DT1 as
    (SELECT EMP_GRADE GRADE,EMP_DEPT DEPT
    FROM DEPARTMENT, EMP_SUBSIDIARY
    WHERE DPT_ID = EMP_DEPT
    AND DPT_AVG_SALARY<500000
    AND EMP_DEPT<'D'
    AND EMP_SALARY<1200000)​
    select * from EMPLOYEE
    where (EMP_GRADE,EMP_DEPT) in
    (select GRADE,DEPT from DT1)​

    Here the following are the query plan of this SQL, it takes 55.9 seconds to finish. The query shows a “Subquery2” with a Nested Loop from sub_emp_salary_inx to DEPARTMENT_PK.
    [​IMG]
    I found the Rows=69606 of step 1 (1 Index Range Scan - EMP_SUBSIDIARY - sub_emp_salary_inx) is significant high, it is not reasonable for MySQL SQL optimizer to such path from EMP_SUBSIDIARY to DEPARTMENT. I believe that MySQL optimizer cannot do a good transitivity improvement DPT_ID. So, I manually add a new condition as “and DPT_ID<'D'“and a “group by 1,2” to narrow down the result set from CTE.

    with DT1
    as (select EMP_GRADE GRADE, EMP_DEPT DEPT
    from DEPARTMENT, EMP_SUBSIDIARY
    where DPT_ID = EMP_DEPT
    and DPT_ID < 'D'
    and DPT_AVG_SALARY < 500000
    and EMP_DEPT < 'D'
    and EMP_SALARY < 1200000
    group by 1,2)
    select *
    from EMPLOYEE
    where (EMP_GRADE,EMP_DEPT)
    in (select GRADE,DEPT from DT1)​

    Here is the query plan of the rewritten SQL and it is running faster. The new query plan shows correct driving path from DEPARTMENT to EMP_SUBSIDIARY, the estimated Rows now are closer to reality. There are two new steps of GROUP and DT1 (materialized) to narrow down the result set of CTE to future improve the performance.
    [​IMG]
    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. There are some other rewrites shown in this screen with comparable results too.
    https://tosska.com/tosska-sql-tuning-expert-tse-for-mysql-2/

    [​IMG]
     
    shabbir likes this.

Share This Page