Derived table materialization: a subquery becomes a temp tableMySQL 8.4 • MariaDB 11.4

Watch a FROM-clause subquery materialize into a temp table, get an auto-index, and then get probed by the outer query.

Lesson familyTemp operator

Parameters (derived table materialization)

About this example
Ready: press Next step, or Play to run it all
0.0s 0.0s

About 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 EXPLAIN for <derived2> or MATERIALIZED to confirm materialization.
  • If derived_merge=on and 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”.