Materialization and Performance
MySQL merges a CTE into the outer query when it can and materializes it when it cannot; that choice decides the plan.
SELECT /*+ MERGE(cte_name) NO_MERGE(other_cte) */ ... FROM cte_name JOIN other_cte ...A CTE is not automatically an optimisation fence in MySQL. The optimizer treats it the same way it treats a derived table or a mergeable view: it either merges the CTE's text into the outer query block, or it materializes the CTE into an internal temporary table and joins against that.
When merging is impossible
Merging is ruled out when the CTE contains any of: an aggregate or window function, DISTINCT, GROUP BY, HAVING, LIMIT, UNION or UNION ALL, a subquery in the select list, an assignment to a user variable, or only literal values. Materialization is also chosen if merging would push the outer block past 61 base tables. A recursive CTE is always materialized.
Why it matters
A merged CTE is free — the plan is identical to writing the query flat, and outer WHERE conditions apply directly to the base tables. A materialized CTE is evaluated once and reused by every reference, which is a win when the step is expensive and used twice, and a loss when it builds a large intermediate that the outer query then filters down to ten rows. MySQL can push outer conditions into a materialized derived table or CTE (derived_condition_pushdown, on by default in 8.4) and may add an automatic index to the temporary table.
Controlling it
The table-level hints MERGE() and NO_MERGE() name the CTE or derived-table alias and override both the optimizer's heuristic and the derived_merge flag of optimizer_switch.
Example
-- cte1 is mergeable (plain projection); cte2 has GROUP BY, so it is materialized.
-- The hints name the FROM-clause aliases, not the CTE names, and state the
-- choice explicitly instead of leaving it to the heuristic.
WITH
recent_orders AS (
SELECT id, customer_id, total
FROM orders
WHERE placed_at >= CURDATE() - INTERVAL 30 DAY
),
customer_ltv AS (
SELECT customer_id, SUM(total) AS lifetime_value
FROM orders
GROUP BY customer_id -- GROUP BY makes merging impossible
)
SELECT /*+ MERGE(r) NO_MERGE(l) */
r.id,
r.total,
l.lifetime_value
FROM recent_orders AS r
JOIN customer_ltv AS l ON l.customer_id = r.customer_id
WHERE r.total > 100;
-- Because recent_orders merges, the placed_at and total conditions are applied
-- together against orders and can use a composite index on (placed_at, total).When to use it
- Diagnose why a query slowed down after a step was extracted into a CTE.
- Force an expensive parsing or aggregation step to run once instead of per reference.
- Decide whether to reuse one CTE twice or repeat the subquery so filters push down.
- Compare EXPLAIN output before and after adding LIMIT to a CTE.
More examples
Reading the plan: merged versus materialized
The two queries differ by one clause and produce different plans. Run both before assuming a CTE is free — a LIMIT added 'just to be safe' during development is the most common accidental fence. A recursive CTE shows `Recursive` in the Extra column of traditional EXPLAIN output.
-- Merged: EXPLAIN shows a plain scan/range on orders, no temporary table.
EXPLAIN FORMAT=TREE
WITH recent AS (
SELECT id, customer_id, total FROM orders WHERE placed_at >= '2026-01-01'
)
SELECT * FROM recent WHERE customer_id = 42;
-- Materialized: LIMIT blocks merging, so the plan gains a materialized step
-- and customer_id = 42 is applied after the temporary table is built.
EXPLAIN FORMAT=TREE
WITH recent AS (
SELECT id, customer_id, total FROM orders
WHERE placed_at >= '2026-01-01'
LIMIT 100000
)
SELECT * FROM recent WHERE customer_id = 42;Forcing materialization to evaluate an expensive step once
Reach for NO_MERGE when the CTE does per-row work that is expensive relative to the row count — JSON extraction, string parsing, a scalar subquery. When materialized, the CTE is built once for the statement no matter how many times it is referenced.
-- Without the hint the optimizer may merge this and re-evaluate the
-- JSON extraction for every reference. NO_MERGE builds it once.
-- The hint must name the alias `p`, not the CTE name `parsed`.
WITH parsed AS (
SELECT id,
JSON_UNQUOTE(JSON_EXTRACT(payload, '$.country')) AS country,
JSON_UNQUOTE(JSON_EXTRACT(payload, '$.plan')) AS plan
FROM events
WHERE created_at >= CURDATE() - INTERVAL 1 DAY
)
SELECT /*+ NO_MERGE(p) */ p.country, p.plan, COUNT(*)
FROM parsed AS p
GROUP BY p.country, p.plan;Turning merging off globally, for comparison
The derived_merge flag is a blunt instrument that also affects views without an explicit ALGORITHM clause. Use it to prove which behaviour a slow query is getting, then encode the decision as a hint on that one statement.
-- Session-level switch: useful for A/B testing a plan, not for production code.
SET SESSION optimizer_switch = 'derived_merge=off';
EXPLAIN FORMAT=TREE
WITH filtered AS (SELECT * FROM orders WHERE status = 'paid')
SELECT COUNT(*) FROM filtered;
SET SESSION optimizer_switch = 'derived_merge=on'; -- back to the default
-- A MERGE/NO_MERGE hint takes precedence over this flag, so prefer the hint
-- when you want one query to behave differently from the rest.
Discussion