Optimizing Subqueries, Derived Tables, View References, and Common Table Expressions What is true for materialization of derived tables is also true for common table expressions (CTEs). In addition, the following considerations pertain specifically to CTEs. If a CTE is materialized by a query, it is materialized once for the query, even if the query references it several times. A recursive CTE is always materialized. If a CTE is materialized, the optimizer automatically adds relevant indexes if it estimates that indexing can speed up access by the top-level statement to the CTE. This is similar to automatic indexing of derived tables, except that if the CTE is referenced multiple times, the optimizer may create multiple indexes, to speed up access by each reference in the most appropriate way. The  MERGE  and  NO_MERGE  optimizer hints can be applied to CTEs. Each CTE reference in the top-level statement can have its own hint, permitting CTE references to be selectively merged or materialized. The following statement uses hints to indicate that  cte1  should be merged and  cte2  should be materialized: The  ALGORITHM  clause for  CREATE VIEW  does not affect materialization for any  WITH  clause preceding the  SELECT  statement in the view definition. Consider this statement: CREATE ALGORITHM={TEMPTABLE|MERGE} VIEW v1 AS WITH ... SELECT ... The  ALGORITHM  value affects materialization only of the  SELECT , not the  WITH  clause. Prior to MySQL 8.0.16, if  internal_tmp_disk_storage_engine=MYISAM , an error occurred for any attempt to materialize a CTE using an on-disk temporary table, since for CTEs, the storage engine used for on-disk internal temporary tables could not be  MyISAM . Beginning with MySQL 8.0.16, this is no longer an issue, since  TempTable  now always uses  InnoDB  for on-disk internal temporary tables. As mentioned previously, a CTE, if materialized, is materialized once, even if referenced multiple times. To indicate one-time materialization, optimizer trace output contains an occurrence of  creating_tmp_table plus one or more occurrences of  reusing_tmp_table . CTEs are similar to derived tables, for which the  materialized_from_subquery  node follows the reference. This is true for a CTE that is referenced multiple times, so there is no duplication of materialized_from_subquery  nodes (which would give the impression that the subquery is executed multiple times, and produce unnecessarily verbose output). Only one reference to the CTE has a complete materialized_from_subquery  node with the description of its subquery plan. Other references have a reduced  materialized_from_subquery  node. The same idea applies to  EXPLAIN  output in TRADITIONAL  format: Subqueries for other references are not shown. MySQL 8.0.22 and later supports derived condition pushdown for eligible subqueries. For a query such as SELECT * FROM (SELECT i, j FROM t1) AS dt WHERE i >  constant , it is possible in many cases to push the the outer  WHERE  condition down to the derived table, in this case resulting in  SELECT * FROM (SELECT i, j FROM t1 WHERE i >  constant ) AS dt . When a derived table cannot be merged into the outer query (for example, if the derived table uses aggregation), pushing the outer  WHERE condition down to the derived table should decrease the number of rows that need to be processed and thus speed up execution of the query. 1706 8.2.2.5 Derived Condition Pushdown Optimization WITH cte1 AS (SELECT a, b FROM table1), cte2 AS (SELECT c, d FROM table2) SELECT /*+ MERGE(cte1) NO_MERGE(cte2) */ cte1.b, cte2.d FROM cte1 JOIN cte2 WHERE cte1.a = cte2.c;