MySQLのCTE

SQLアンチパターンをパラパラめくっていて、 ナイーブツリーの項で

SQL-99標準では、WITHキーワードの後に共通テーブル式（CTE：common table expression）を指定する形式の再帰クエリ構文を定義しています。

という一文を目にし、ふとMySQL 8からCTEが使えるようになった事を思い出したので今更試してみた。

データを入れる

create table family (id, parent_id, name) ;

insert into family (id, parent_id, name) values 
(1, NULL, '家康'),
(2, 1, '秀忠'),
(3, 1, '義直'),
(4, 1, '頼信'),
(5, 1, '頼房'),
(6, 2, '家光'),
(7, 6, '家綱'),
(8, 6, '綱重'),
(9, 6, '綱吉'),
(10, 8, '家宣'),
(11, 4, '光貞'),
(12, 11, '吉宗');

秀忠の子孫を取る

with recursive family_tree (id, parent_id, name, depth) as (
  select *, 0 as depth from family where id = 2
  union all
  select f.*, ft.depth + 1 as depth from family_tree ft inner join family f on ft.id = f.parent_id
)
select * from family_tree;

+------+-----------+--------+-------+
| id   | parent_id | name   | depth |
+------+-----------+--------+-------+
|    2 |         1 | 秀忠   |     0 |
|    6 |         2 | 家光   |     1 |
|    7 |         6 | 家綱   |     2 |
|    8 |         6 | 綱重   |     2 |
|    9 |         6 | 綱吉   |     2 |
|   10 |         8 | 家宣   |     3 |
+------+-----------+--------+-------+
6 rows in set (0.03 sec)

吉宗のご先祖様を取る

with recursive family_tree (id, parent_id, name, depth) as (
  select *, 0 as depth from family where id = 12
  union all
  select f.*, ft.depth - 1 as depth from family_tree ft  inner join family f on ft.parent_id = f.id
)
select * from family_tree;

+------+-----------+--------+-------+
| id   | parent_id | name   | depth |
+------+-----------+--------+-------+
|   12 |        11 | 吉宗   |     0 |
|   11 |         4 | 光貞   |    -1 |
|    4 |         1 | 頼信   |    -2 |
|    1 |      NULL | 家康   |    -3 |
+------+-----------+--------+-------+
4 rows in set (0.00 sec)

とりあえずexplainしてみる

mysql> explain with recursive family_tree (id, parent_id, name, depth) as (select *, 0 as depth from family where id = 2   union all     select f.*, ft.depth + 1 as depth from family_tree ft inner join family f on ft.id = f.parent_id ) select * from family_tree;
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table      | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | NULL                                               |
|  2 | DERIVED     | family     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |    10.00 | Using where                                        |
|  3 | UNION       | ft         | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |   100.00 | Recursive                                          |
|  3 | UNION       | f          | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   12 |    10.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+------------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

あっ、Using join buffer (Block Nested Loop)、parent_idにindex付けないと