When our team finally moved the last 5.7 replica set to 8.0, the first thing engineers did was rewrite queries. Nested derived tables became CTEs. Greatest-n-per-group hacks with self-joins became ROW_NUMBER. The code review diffs were beautiful. Two of the rewrites also took p99 latency from 40 ms to multiple seconds, and both regressions shipped because everyone assumed a cleaner query is a faster query.
It is not. CTEs and window functions in MySQL are planning constructs with specific execution strategies, and each strategy has a cliff. These notes cover the three cliffs I now check for in every review: derived table merging versus materialization, window sort and frame costs, and the handful of cases where the ugly 5.7-era JOIN is still the right answer on 8.4.
A CTE is not a hint, and not always a temp table
The most common mental model I hear — a CTE is materialized once and reused — is only sometimes true in MySQL. A non-recursive CTE referenced exactly once is a candidate for the same treatment as any derived table: the optimizer can merge it into the outer query block, exactly as if you had written the subquery inline. Merging is controlled by the derived_merge flag in optimizer_switch, which is on by default.
Merging is usually what you want, because a merged CTE exposes its base tables to the outer query's optimizer: indexes stay usable, join order stays flexible, and outer WHERE predicates apply directly. Materialization builds an internal temp table first and joins to that instead — and a temp table has no statistics and none of your carefully built indexes.
Merging is impossible when the CTE body contains constructs that change row multiplicity or ordering semantics: aggregation or GROUP BY, DISTINCT, LIMIT, UNION, or — importantly for this post — window functions. Any CTE with a window function inside it will be materialized. Stack a window CTE under three more CTEs and you have built a pipeline of temp tables, each one opaque to the optimizer.
Read the plan, not the query text
You cannot tell merged from materialized by looking at the SQL, so I make EXPLAIN part of the rewrite, not part of the postmortem. EXPLAIN FORMAT=TREE is the fastest read in 8.0-era servers.
EXPLAIN FORMAT=TREE
WITH recent AS (
SELECT customer_id, MAX(created_at) AS last_order
FROM orders
WHERE created_at >= NOW() - INTERVAL 90 DAY
GROUP BY customer_id
)
SELECT c.id, c.email, r.last_order
FROM customers AS c
JOIN recent AS r ON r.customer_id = c.id
WHERE c.region = 'eu-west';
-- Look for lines like:
-- -> Table scan on r
-- -> Materialize CTE recent
-- versus the CTE's base tables appearing inline in the join tree.Two things soften the materialization penalty, and both are worth confirming in the plan. First, a materialized CTE referenced multiple times is computed once and shared, which can genuinely beat repeating an expensive derived table twice. Second, the optimizer can add an auto-generated index on a materialized derived table or CTE to support the join condition — the plan shows an index lookup on the temp table instead of a scan. When that auto-key does not appear, a big outer table joining to an unindexed temp table degenerates fast.
When the optimizer's default is wrong, say so explicitly: the MERGE and NO_MERGE optimizer hints override derived_merge per table. I reach for NO_MERGE when merging duplicates an expensive expression into multiple predicates, and MERGE when a once-referenced CTE got materialized only because someone left a needless DISTINCT inside it. Removing the blocker beats hinting around it.
Window functions: you pay in sorts and buffering
A window function executes after joins, WHERE, and grouping, over the rows the query has already produced. The dominant cost is ordering: each distinct PARTITION BY plus ORDER BY combination needs the rows arranged that way, which means a filesort unless an index already delivers the order — and after a join, it usually does not. Three window specs with three different orderings mean three sort passes over the result set.
My first rule for window-heavy queries: consolidate window definitions. Use a named WINDOW clause, make functions share partitioning and ordering where the logic allows, and check the TREE plan for how many sort steps survive. On string keys, sort cost also scales with collation weight complexity — sorting on a utf8mb4 accent- and case-insensitive collation is measurably heavier per row than binary comparison, which is one more reason the collation choices post keeps paying rent.
The second cost is the frame. Aggregates over a frame like ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW can be computed incrementally as the engine streams through the partition. RANGE frames with peer groups and moving two-sided frames force the engine to buffer partition rows and re-evaluate boundaries. The trap is that the default frame — when you write ORDER BY inside OVER and no explicit frame — is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, not ROWS. If you mean ROWS, write ROWS; on partitions with many ties it changes both the result and the cost. And a partition-sized frame on a partition with millions of rows means the temp buffer spills to disk. Watch Created_tmp_disk_tables when a window query is under suspicion.
The rewrite that almost always wins: correlated subqueries
The best return on window functions is killing correlated subqueries that execute once per outer row. The classic latest-row-per-group pattern:
-- Before: runs the inner query per outer row.
SELECT o.*
FROM orders AS o
WHERE o.created_at = (
SELECT MAX(i.created_at)
FROM orders AS i
WHERE i.customer_id = o.customer_id
);
-- After: one pass, one sort, no correlation.
WITH ranked AS (
SELECT o.*,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY created_at DESC
) AS rn
FROM orders AS o
)
SELECT * FROM ranked WHERE rn = 1;On a table where each customer has many orders, the window version is a different complexity class: one scan plus one sort instead of an index dive per row — and unlike the MAX-equality version, it does not return duplicate rows on timestamp ties. The same shape replaces per-row COUNT and SUM correlations with a single windowed pass.
The honest caveat: the window version reads the whole table. The correlated version's cost is proportional to the outer row count times an index lookup. Which wins depends on how much of the table you actually need — and that is the boundary of the next section.
When the old JOIN is still faster
- Selective outer filters. If you want the latest order for fifty specific customers, a correlated subquery backed by an index on (customer_id, created_at) does fifty descending index dives. The window CTE sorts every order for every customer and then throws away almost all of it. LATERAL derived tables (8.0.14+) give the same per-row shape with cleaner syntax.
- The predicate cannot push down. WHERE rn = 1 is applied after the materialized CTE is fully built. MySQL 8.0.22+ can push some outer conditions into materialized derived tables, but rank-based filters still require producing the ranked set first. A JOIN against a grouped aggregate lets the optimizer filter earlier.
- Index-ordered top-N. ORDER BY indexed_col LIMIT 10 with no window reads ten rows. Any windowed formulation of the same result sorts the lot.
- Chained window CTEs. Each materialization boundary hides statistics from the next join. Two levels is usually fine; four is a query I will be paged about.
None of this says avoid window functions — it says the readable rewrite and the fast plan are separate claims, and each rewrite needs its own EXPLAIN. Queries over JSON-derived values deserve extra suspicion, since extraction inside a materialized CTE runs per row with no index support; the indexing side of that story is in the MySQL JSON vs PostgreSQL JSONB notes.
Catching the regressions before users do
Both of our 8.0 rewrite regressions were visible in the same three signals: rows examined per statement digest jumped an order of magnitude, temp disk table creation climbed, and sort_merge_passes went from zero to constant. If you track statement digests over time, a CTE that flipped from merged to materialized after an innocent edit shows up as a step change days before anyone files a ticket.
Where MonPG stands on this, stated plainly: MonPG is a PostgreSQL monitoring platform today, and MySQL support is in active development — we do not monitor MySQL yet. Digest-level history, temp-table pressure, and sort regressions for MySQL are precisely what we are building; the MySQL monitoring (coming soon) page is where to follow it or tell us what your incident reviews need. If PostgreSQL is also part of your fleet, that side is live now and you can start there — window function costs and CTE materialization have close cousins in Postgres, and the same evidence-first workflow applies.