Derived Table MaterializationMySQL 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)

Rows produced by the FROM-clause subquery.
Average size of each materialized row.
Rows in the outer query that probe the temp table.
MySQL can auto-generate a B+tree index on the join key of the temp table.

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;

MySQL materializes the grouped subquery into a temp table, optionally adds an auto-index, then probes it from the outer query.

What you'll see in the animation

  • The subquery executes fully, writing rows into a temporary table (yellow).
  • If the temp table exceeds 16 MiB (tmp_table_size), it spills to disk.
  • MySQL may auto-generate a B+tree index on the join column for fast lookups.
  • The outer query probes the temp table for each of its rows.
  • Matched results flow out as green result tuples.
Ready — press Play
0.0s 0.0s

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? ?MySQL can auto-generate a B+tree index on the join key for faster lookups.

—

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 (since MySQL 5.7, refined in 8.0): when the outer query has an equi-join condition on the derived table, MySQL automatically creates a hash or B+tree index on the temp table's join column. This turns an O(n) full-scan probe into an O(log n) indexed lookup per outer row.

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 ensure the join column has an index hint for the auto-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”.