Parameters (derived table materialization)
About this exampleAbout this example
Example query this animation executes
-- Derived table: subquery materialized into tmp
SELECT d.dept_name, stats.avg_salary
FROM departments d
JOIN (SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id) AS stats
ON d.id = stats.dept_id;
The grouped subquery cannot be merged, so the server materializes it into a temp table (with a key on dept_id), then probes it once per departments row.
What you'll see in the animation
- Top: the temp table stats. The key on dept_id is in place first; then each subquery row (dept_id and its average salary) fills a dashed slot.
- The bar under stats shows its size against tmp_table_size (16 MiB). Past it, stats moves to an on-disk table.
- Lower left: departments. A purple pill such as "Eng id=3" travels up to the stats row with that dept_id, one lookup through the key.
- A green pill carries the joined row down to the result rows (lower right).
- Next step writes or probes one row at a time; the simplified code beside the drawing highlights the lines each step runs.
Cost readout (derived table materialization)
Subquery rows ?Total rows produced by the FROM-clause subquery.
—
Materialization writes ?Rows written into the temp table during materialization.
—
Temp table size ?Estimated size of the materialized temp table (rows × row_size).
—
Spills to disk? ?If the temp table exceeds 16 MiB, it spills to an on-disk temp table.
—
Auto-index? ?The key the optimizer adds on the join column (dept_id) so each outer row does one lookup.
—
Probe reads ?Read operations when the outer query probes the temp table.
—
Total I/O ?Materialization writes + probe reads.
—
Materialized (indexed vs scan) vs merged (log–log)
Learn more — derived table materialization vs merging
The derived_merge optimizer switch (ON by default since MySQL 5.7)
lets the optimizer merge a derived table into the outer query, eliminating the
temp table entirely. When merge is possible, there is zero materialization
overhead — the subquery's tables are accessed directly.
When can't MySQL merge? Materialization is the fallback when
the subquery contains: GROUP BY, DISTINCT,
LIMIT, UNION, aggregate functions, or
user-defined variables. In these cases, the result must be fully computed
before the outer query can use it.
Auto-key generation: when the outer query joins the
derived table with an equality, the optimizer adds an index on the join
column to the temp table (EXPLAIN shows key <auto_key0>
in MySQL, key0 in MariaDB). The key is planned while the join
is optimized, so the table is created with it and every row is indexed as
it is written. This turns a read of the whole temp table into one indexed
lookup per outer row. MariaDB can turn it off with
optimizer_switch='derived_with_keys=off'; MySQL has no switch
for it.
MariaDB split materialization: for this exact shape (a
grouped derived table joined on its GROUP BY column), MariaDB may instead
compute the average only for the departments the outer query needs, if
employees(dept_id) has an index. EXPLAIN then says
LATERAL DERIVED (optimizer_switch
split_materialized, on by default).
Temp table sizing: The materialized temp table starts in
memory. If it exceeds tmp_table_size (default 16 MiB), it spills
to disk. Large derived tables with many rows or wide rows are more likely
to spill, adding disk I/O overhead.
Optimization tips:
- Check
EXPLAINfor<derived2>orMATERIALIZEDto confirm materialization. - If
derived_merge=onand it still materializes, the subquery likely has GROUP BY/DISTINCT/LIMIT preventing merge. - Consider rewriting the query to avoid the derived table, or make sure the outer query joins it with an equality so the optimizer can add a key.
Sources: MySQL 8.4 reference manual §10.2.2.4 “Optimizing Derived Tables”; MySQL 8.4 reference manual §7.1.8 “Server System Variables”.