The query that started this post came from an ORM. It joined the orders table to a subquery in the FROM clause that aggregated six months of events, and it took eleven minutes. The hand-rolled rewrite, same results, aggregation restricted to only the keys the outer query needed, took 300 milliseconds. Nothing about the data changed. The only difference was whether MySQL could merge the derived table into the outer query or had to materialize it first.
Derived tables are one of those features that work transparently until they very much do not. Here is how the decision is made on MySQL 8.0 and 8.4, how to see which path you are on, and the rewrites I reach for.
Merge or materialize: the two strategies
A derived table is a subquery in the FROM clause: SELECT ... FROM (SELECT ...) AS dt. MySQL has two ways to execute it. It can merge the subquery into the outer query, treating the whole thing as one query to optimize jointly — predicates push down, join order is chosen globally, indexes apply everywhere. Or it can materialize: execute the subquery once, store the result in an internal temporary table, and treat that temp table as if it were a real table for the rest of the plan.
Merging is controlled by derived_merge in optimizer_switch, and it has been on by default since MySQL 5.7, which is why many engineers have never seen the old always-materialize behavior. But merge is only legal when the subquery's semantics allow it. Aggregate functions, GROUP BY, DISTINCT, LIMIT, UNION, window functions, and HAVING over aggregates all block merging, because folding them into the outer query would change the results. The moment your derived table contains a GROUP BY — and in real life it usually does — you are on the materialization path.
Why materialization kills
Materialization is not inherently bad. If the subquery produces 200 rows and the outer query joins them ten times, materializing once and scanning the temp table is the efficient plan; the optimizer can even add an automatically generated index on the materialized table when that speeds up the join. The disaster case is the opposite shape: the subquery produces millions of rows, the outer query's predicates would have restricted the work to thousands, but because the derived table is opaque, those predicates get applied only after the temp table is built — unless the optimizer can push them down into it, the 8.0.22 escape hatch covered below. You pay to aggregate or scan everything, then throw 99 percent of it away.
There is a second, quieter cost. A big derived table is a big internal temporary table, and those have their own memory limits and spill behavior through the TempTable engine. Under 8.0 defaults, a temp table that outgrows its memory budget overflows into memory-mapped files in tmpdir; under 8.4 defaults the mmap path is off, so the table converts straight to an InnoDB on-disk internal temporary table instead. Either way "the query is slow" turns into "the query is slow and the disk is filling up." I covered that fallout in the internal temp tables post.
Seeing it in EXPLAIN
Materialized derived tables show up in EXPLAIN with select_type DERIVED; merged ones simply disappear into the outer plan, which is itself the tell. EXPLAIN ANALYZE in 8.0 makes the cost impossible to ignore, because you get the actual time spent filling the temp table:
-- is merging enabled?
SELECT @@optimizer_switch;
-- where does the time actually go?
EXPLAIN ANALYZE
SELECT o.customer_id, t.total_events
FROM orders o
JOIN (
SELECT customer_id, COUNT(*) AS total_events
FROM events
GROUP BY customer_id
) t ON t.customer_id = o.customer_id
WHERE o.created_at >= '2026-06-01'
AND o.created_at < '2026-07-01';Read the timings inside out: if nearly all the cost sits in the Materialize step — the DERIVED select_type you would see in traditional EXPLAIN output — and the outer query then filters the result down to a handful of rows, you have the bad shape. For reading the timing output itself, the EXPLAIN ANALYZE guide walks through it line by line.
Derived condition pushdown, the partial rescue
Since MySQL 8.0.22 there is a middle path: derived_condition_pushdown, also in optimizer_switch and on by default. When the derived table cannot be merged, the optimizer can still push outer WHERE conditions down into it when they reference the subquery's columns directly, so a predicate like dt.customer_id = 42 gets evaluated while the derived table is being built instead of after. It does not help with join conditions, only standalone predicates, and a GROUP BY limits what can be pushed. Check your server version; a surprising number of "impossible" performance bugs from early 8.0 point releases simply vanish after an upgrade.
The rewrites that actually fix it
When pushdown cannot save you, rewrite. Three patterns cover most of what I meet. First, aggregate only what you need: carry the restriction into the subquery by hand. If the outer query only wants customers with June orders, the derived table should aggregate events for those customers and no one else — drive it off the key set, something like WHERE customer_id IN (SELECT customer_id FROM orders WHERE created_at >= '2026-06-01' AND created_at < '2026-07-01') — instead of aggregating the whole events table and discarding most of it at the join. Move predicates, not meanings: filtering the events themselves to June would change what COUNT(*) counts. ORMs will not do this for you; you do it in the query text.
Second, replace the derived table with a correlated subquery or an EXISTS when the outer side is selective. A per-row correlated subquery looks ugly and benchmarks beautifully when the outer side produces ten rows and the inner side has the right index.
Third, LATERAL derived tables, supported since MySQL 8.0.14: the LATERAL keyword lets the derived table reference columns from preceding FROM items, so you get per-row correlation with join syntax. It is the cleanest way to express "for each customer, their own top-N or aggregate" without materializing the world. One caution: a LATERAL derived table cannot be merged or pushed down by design, so it wins exactly when per-row execution over good indexes beats one big materialization. Measure with EXPLAIN ANALYZE rather than arguing from theory.
And the last-resort knob: setting optimizer_switch to disable derived_merge exists, and I have used it exactly once, when merging produced a pathological join order that hints could not fix. Treat it as a diagnostic, not a fix — global optimizer_switch flips are how you trade one bug for three new ones, as the optimizer_switch pitfalls post lays out.
Same-day visibility for plan flips
The ORM incident that opened this post took weeks to diagnose because nobody was watching the right number. That class of miss is why the MySQL monitoring MonPG is building puts rows examined versus rows returned per statement front and center, with temp-table spill volume alongside: an ORM update that flips a hot query onto the materialization path should show up the day it ships, not after a quarter of complaints. To be clear about scope: MonPG monitors PostgreSQL today, with MySQL support in active development, coming soon. The finished workflow for Postgres is at MonPG for PostgreSQL, and the rest of the MySQL series is on the blog.