MySQL 8.0: Improved performance with CTE

MySQL 8.0 introduces Common Table Expressions (CTE). My colleague Guilhem has written several blog posts on how to use CTEs , and you can also read about it in the MySQL 8.0 Reference Manual. In this blog post, I will focus on how using a CTE instead of a view or a derived table can improve performance.

As usual, my example query will be a query from the DBT-3 benchmark. This time, we will look at Query 15, the Top Supplier Query:

This query is referring a view, revenue0, twice; both in the FROM clause and in a subquery in the WHERE clause. The view is defined as follows:

This view finds the total revenue for each supplier during a specified 90 day period. Since the view contains a GROUP BY clause, MySQL can not merge the view into the query where it is used. Instead, it will execute the view and store the result in a temporary table. Visual EXPLAIN in MySQL Workbench shows the following query plan for Query 15:

From the diagram we see that the view is executed and materialized twice; once for each reference.

To use a CTE instead of a view, we just put the view definition it in front of the query, using a WITH clause:

Visual EXPLAIN shows that the CTE is now only materialized once:

In other words, MySQL will use a single temporary table for multiple references to a CTE. For Query 15, the materialization is by far the most time consuming part of the execution. Hence, the execution time is almost cut in half by doing the materialization only once:

One thought on “MySQL 8.0: Improved performance with CTE

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter * Time limit is exhausted. Please reload CAPTCHA.