> ## Documentation Index
> Fetch the complete documentation index at: https://villagesql.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# When a MySQL CTE beats a derived table

> Whether the MySQL optimizer inlines a CTE or materializes it, how to tell from EXPLAIN, and when a CTE is the wrong shape for the query you are writing.

> [card] [VillageSQL is a drop-in replacement for MySQL with extensions.](https://villagesql.com/docs/en/8.4/stable/install-server.md)
>
> All examples on this page work on VillageSQL. Install Now →

A CTE and a derived table return the same rows, so the choice between them is
usually about reading. It stops being only about reading when the optimizer
materializes one, which it does more often than most write-ups suggest. This
guide is about telling which you got, and about the cases where a CTE is the
wrong shape.

The tutorial teaches the syntax itself:
[common table expressions](https://villagesql.com/docs/en/tutorial/cte.md), [recursive CTEs](https://villagesql.com/docs/en/tutorial/recursive-cte.md)
and [derived tables](https://villagesql.com/docs/en/tutorial/derived-tables.md).

Every plan on this page came from 1 server against the Sakila sample database.
Costs and row estimates come from InnoDB's sampled statistics and from your
`optimizer_switch` settings, so your numbers will differ. The shape of each
plan is the part that transfers.

## The rule: mergeable or not

A CTE is either folded into the outer query or built as a temporary table
first, and which you get is decided by what the CTE contains rather than by how
many times you name it.

A CTE whose body is a plain `SELECT` with `WHERE` and `JOIN` merges, and
disappears from the plan entirely:

```sql theme={null}
EXPLAIN FORMAT=TREE WITH g AS (SELECT film_id, title, rating FROM film WHERE rating = 'G')
SELECT title FROM g WHERE film_id < 50\G
```

```text theme={null}
-> Filter: ((film.rating = 'G') and (film.film_id < 50))  (cost=10.1 rows=9.8)
    -> Index range scan on film using PRIMARY over (film_id < 50)  (cost=10.1 rows=49)
```

No CTE node, and the outer condition reached the index. A CTE containing
`GROUP BY`, `DISTINCT`, `LIMIT`, `UNION`, or a window function cannot merge,
because the outer query's conditions are not equivalent when applied before
those operations. Such a CTE is materialized.

The plans below show that second case. Read them as being about the CTE's
contents, not about the word `WITH`.

## Check which one you got

`EXPLAIN FORMAT=TREE` says so in as many words. The default `EXPLAIN` does not:
its `Extra` column does not carry the word on the queries here.

```sql theme={null}
EXPLAIN FORMAT=TREE WITH counts AS (SELECT rating, COUNT(*) AS films FROM film GROUP BY rating)
SELECT rating, films FROM counts WHERE films > 200\G
```

```text theme={null}
-> Table scan on counts  (cost=2.5..2.5 rows=0)
    -> Materialize CTE counts  (cost=0..0 rows=0)
        -> Filter: (count(0) > 200)
            -> Table scan on <temporary>
                -> Aggregate using temporary table
                    -> Table scan on film  (cost=103 rows=1000)
```

`Materialize CTE counts`, even though the CTE is named once. The `GROUP BY`
inside it is what forces that, and the server writes the rows to a temporary
table and reads them back.

**A derived table containing the same `GROUP BY` gets the same plan.** Written
as `FROM (SELECT rating, COUNT(*) ... GROUP BY rating) AS counts`, the tree is
identical apart from the node's name, and the cost is the same. So on a
single-reference query the CTE beats the derived table by nothing: the 2 forms
differ in how they read, and the optimizer treats them alike.

Reference the same CTE twice and the wording changes to `if needed`, with the
second reference reusing the same materialized result:

```sql theme={null}
EXPLAIN FORMAT=TREE WITH counts AS (SELECT rating, COUNT(*) AS films FROM film GROUP BY rating)
SELECT a.rating, a.films FROM counts AS a JOIN counts AS b ON a.films = b.films\G
```

```text theme={null}
-> Nested loop inner join  (cost=2502 rows=0)
    -> Table scan on a  (cost=2.5..2.5 rows=0)
        -> Materialize CTE counts if needed  (cost=0..0 rows=0)
            -> Table scan on <temporary>
                -> Aggregate using temporary table
                    -> Table scan on film  (cost=103 rows=1000)
    -> Index lookup on b using <auto_key0> (films=a.films)  (cost=0.25..2.5 rows=10)
        -> Materialize CTE counts if needed (query plan printed elsewhere)  (cost=0..0 rows=0)
```

That is where the 2 forms genuinely part company. The expensive inner query
runs once and both references read the result. Written as derived tables the
same query names the subquery twice, and computes it twice.

## What materializing costs you

A materialized CTE is a temporary table, which means 2 things worth knowing.

It gives up the base tables' indexes. Once the rows sit in a temporary table,
the outer query scans that table. The server may build an `auto_key` on the
fly, as it did for the join above, and that is not the index you had on the
real table. Compare the merged plan at the top of this page, where the outer
condition reached `PRIMARY`.

Pushdown softens this. `derived_condition_pushdown` is on by default and moves
what it can inside, which is why `Filter: (count(0) > 200)` appears *inside*
the `Materialize` node above rather than outside it. It cannot move everything,
so when a plan shows the CTE producing far more rows than the result keeps,
write the restriction inside the CTE yourself.

## When a CTE is the wrong shape

* **A single short step.** A derived table keeps it where it is used. Naming a
  1-line subquery adds a hop for the reader.
* **A filter the optimizer did not push down.** If the plan shows the CTE
  materializing every row and the outer query discarding most of them, write
  the filter inside.
* **Row-by-row work against an index.** A correlated subquery or a join can use
  the index on each lookup. A materialized CTE has already given that up.

A CTE is the right shape when the query has 2 or more named stages, when the
same result is needed twice, or when the alternative is a subquery nested deep
enough that nobody can see where it ends.

It is also the easier of the 2 to debug. A CTE definition can be run on its own
by selecting from it, which tells you whether the first stage is right before
you look at the second. Extracting a derived table from the middle of a `FROM`
clause to run it by hand is the same work done manually every time.

## Recursive CTEs on real hierarchies

Sakila has no hierarchy, so this section uses a table of its own. Create it
beside the sample data and run `DROP TABLE employees;` when you have finished
with it:

```sql theme={null}
CREATE TABLE employees (
    id         INT PRIMARY KEY,
    name       VARCHAR(50) NOT NULL,
    manager_id INT NULL
);

INSERT INTO employees VALUES
    (1,'Ada',NULL), (2,'Brem',1), (3,'Chi',1), (4,'Dov',2), (5,'Eze',4);
```

The seed selects the roots, and the recursive member joins the table to the
rows already produced. The `depth` column is both the answer and the guard.

```sql theme={null}
WITH RECURSIVE org_chart AS (
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE manager_id IS NULL
  UNION ALL
    SELECT e.id, e.name, e.manager_id, oc.depth + 1
    FROM employees AS e
    INNER JOIN org_chart AS oc ON e.manager_id = oc.id
    WHERE oc.depth < 100
)
SELECT depth, name FROM org_chart ORDER BY depth, name;
```

```text theme={null}
+-------+------+
| depth | name |
+-------+------+
|     0 | Ada  |
|     1 | Brem |
|     1 | Chi  |
|     2 | Dov  |
|     3 | Eze  |
+-------+------+
5 rows in set (0.00 sec)
```

**Cycles are the failure mode.** If A reports to B and B reports to A, nothing
in the query stops, and it runs until it hits the depth limit:

```text theme={null}
ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.
```

`WHERE oc.depth < 100` in the query above is what prevents that. It turns an
unbounded walk into a bounded one, and it works whether or not the data is
clean.

The error message suggests raising `cte_max_recursion_depth`, which is right
only when the recursion genuinely terminates and needs more rounds than
the default 1,000. A legitimate 1,500-step series hits the same error, and
`SET SESSION cte_max_recursion_depth = 5000` is the correct answer there. On
cyclic data it is the wrong one: the query still never ends, and fails later instead.

## Availability and terminology

CTEs arrived in MySQL 8.0 and are present in every release since, including
8.4 and 9.x. A server that answers `ERROR 1064` to a `WITH` clause predates
them; check with `SELECT VERSION()`.

The standard calls the first part of a recursive CTE the **anchor member** and
the second the **recursive member**. The tutorial calls the first one the seed,
which is the same thing, and MySQL's own messages use the standard terms.

## CTEs work in INSERT, UPDATE, and DELETE

```sql theme={null}
WITH stale_orders AS (
    SELECT id FROM orders WHERE created_at < NOW() - INTERVAL 1 YEAR
)
DELETE FROM orders WHERE id IN (SELECT id FROM stale_orders);
```

The CTE is evaluated first, so the rows it names are decided before the
statement changes anything.

## Troubleshooting

| Problem | Cause and fix |
| :- | :- |
| A CTE is slower than the subquery it replaced | It materialized. Check `EXPLAIN FORMAT=TREE` for `Materialize CTE`. |
| The CTE reads far more rows than the result needs | The outer filter was not pushed down. Move it inside the CTE. |
| `ERROR 1146: Table '<database>.cte_name' doesn't exist` | The CTE references itself without `RECURSIVE`. Write `WITH RECURSIVE`. |
| `ERROR 3636` on data you expect to terminate | Cycles in the data. Add a depth column and a `WHERE depth < N` guard. |
| `ERROR 3636` on a genuinely deep series | Raise `cte_max_recursion_depth` for the session. |
| `ERROR 1064` on `WITH` | The server predates CTE support. Check `SELECT VERSION()`. |

## See also

* [Subqueries vs JOINs in MySQL](https://villagesql.com/docs/en/guides/subqueries-vs-joins.md) — the other shape this competes with
* [MySQL GROUP BY performance](https://villagesql.com/docs/en/guides/group-by-having.md) — the temporary table a CTE can help you avoid
* [MySQL views](https://villagesql.com/docs/en/guides/views.md) — persisting a named query as a schema object instead
* [Common table expressions](https://villagesql.com/docs/en/tutorial/cte.md) — the tutorial, if you want the syntax first


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.