MySQL CTE가 파생 테이블보다 나은 경우

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

-> 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)

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

-> 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)

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

-> 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)

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);

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;

+-------+------+
| depth | name |
+-------+------+
|     0 | Ada  |
|     1 | Brem |
|     1 | Chi  |
|     2 | Dov  |
|     3 | Eze  |
+-------+------+
5 rows in set (0.00 sec)

ERROR 3636 (HY000): Recursive query aborted after 1001 iterations. Try increasing @@cte_max_recursion_depth to a larger value.

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);