How MySQL Executes Recursive CTEs

This article explains how MySQL implements and executes recursive Common Table Expressions (CTEs). We will cover the internal mechanics of recursive queries, including how MySQL uses temporary tables to manage intermediate states, the step-by-step execution loop of anchor and recursive members, and the system variables that control execution limits to prevent infinite loops.

The Structure of a Recursive CTE

In MySQL 8.0 and later, a recursive CTE is defined using the WITH RECURSIVE syntax. It consists of two main components separated by a set operator, typically UNION ALL or UNION:

  1. The Anchor Member: A non-recursive select statement that generates the initial base result set.
  2. The Recursive Member: A select statement that references the CTE itself. This member reads the output of the previous iteration to generate new rows.
WITH RECURSIVE cte_name AS (
    -- Anchor Member
    SELECT ... 
    UNION ALL
    -- Recursive Member
    SELECT ... FROM cte_name WHERE ...
)
SELECT * FROM cte_name;

How MySQL Implements Recursive CTEs Internally

MySQL executes recursive CTEs using memory-based or disk-based internal temporary tables. Because a recursive query must build upon the results of its previous step, MySQL manages the execution state using two specific temporary tables:

By separating the reading and writing phases into these two tables, MySQL avoids reading from and writing to the same dataset simultaneously.

Step-by-Step Execution Flow

When MySQL executes a recursive CTE, it follows a strict iterative loop:

Step 1: Execute the Anchor Member

MySQL runs the anchor member first. The resulting rows are written to two places: * The final query result accumulator. * The Working Table, which prepares the data for the first recursive step.

Step 2: Execute the Recursive Member

MySQL executes the recursive member. When the query references the CTE name, MySQL reads the data from the Working Table. The output generated by this step is written to the Intermediate Table.

Step 3: Evaluate and Swap

MySQL checks the Intermediate Table to determine the next action: * If the Intermediate Table is empty: The recursion has reached its natural end. The loop terminates, and MySQL returns the accumulated result set to the client. * If the Intermediate Table contains rows: 1. The rows in the Intermediate Table are appended to the final query result accumulator. 2. The Working Table is cleared. 3. The contents of the Intermediate Table are moved (swapped) into the Working Table. 4. The Intermediate Table is cleared.

Step 4: Repeat

MySQL returns to Step 2 and runs the recursive member again using the updated Working Table.

Recursion Safety Safeguards

Because recursive queries can easily fall into infinite loops if the termination condition is poorly written, MySQL enforces safety limits during execution: