How MySQL Processes Derived Tables in FROM Clause

This article explains how the MySQL query optimizer handles derived tables—subqueries specified within the FROM clause of a SQL statement. It covers the two main processing strategies: merging the derived table into the outer query and materializing it into an internal temporary table, detailing how MySQL decides between these approaches to optimize performance.

A derived table is a subquery in the FROM clause that acts as a virtual table for the outer query. Because these subqueries can contain complex logic, the MySQL optimizer must determine the most efficient way to execute them. To do this, it employs two primary strategies: Merging and Materialization.

The Merge Strategy

When using the merge strategy, the optimizer integrates the derived table’s query block directly into the outer query block. This process rewrites the query internally, eliminating the need to create a physical temporary table and allowing the optimizer to consider a wider range of join orders and indexing options.

MySQL prefers the merge strategy because it avoids the overhead of writing and reading temporary data. However, the optimizer can only merge a derived table if it does not contain constructs that prevent merging. Factors that prevent the merge strategy include:

• Aggregate functions (such as SUM(), MIN(), or COUNT())
• DISTINCT
• GROUP BY
• HAVING
• LIMIT
• UNION or UNION ALL

If any of these clauses are present in the subquery, MySQL cannot merge the derived table and must fall back to materialization.

The Materialization Strategy

If merging is not possible, MySQL uses the materialization strategy. In this scenario, the optimizer executes the subquery and stores the resulting rows in an internal temporary table.

This temporary table is initially created in memory using the TempTable or MEMORY storage engine. If the dataset size exceeds the configured limits (such as tmp_table_size or max_heap_table_size), MySQL writes the temporary table to disk using the InnoDB storage engine.

To optimize subsequent joins with other tables in the query, MySQL often automatically creates an index on the columns of the materialized table. This on-the-fly indexing significantly speeds up data retrieval during the execution of the outer query.

Controlling Optimizer Decisions

You can control whether MySQL attempts to merge derived tables using the optimizer_switch system variable. The derived_merge flag, which is enabled by default, controls this behavior.

To disable the merge strategy globally or for a specific session, you can adjust the setting:

SET optimizer_switch = 'derived_merge=off';

Additionally, you can override this behavior on a query-by-query basis using optimizer hints. The /*+ MERGE(...) */ and /*+ NO_MERGE(...) */ hints allow you to force or prevent merging for a specific derived table within your SQL statement.