Can EXPLAIN be used to get some insights about Common Table Expression (CTE)?

Question

I need to find the ancestors in a table having an ID value and a Google research point me to recursive Common Table Expression (CTE) I wonder if EXPLAIN can be used to get some insights on how MySQL handles this and if there is room for optimization.

Also is this the best strategy to get ancestors in a table? I could involve code in here but don't want to because is gonna lead me to loops and probably multiple SELECT statements sent to the DB.

The SQL I wrote following the previous article is as follow:

WITH RECURSIVE page_revisions_path (id, page_id, parent_id) AS
(
 SELECT id, page_id, parent_id
 FROM page_revisions
 WHERE parent_id = 'some_id'
 UNION ALL
 SELECT c.id, c.page_id, c.parent_id 
 FROM page_revisions_path AS cp JOIN page_revisions AS c ON cp.id = c.parent_id
)

SELECT * FROM page_revisions_path;

Here is also the SHOW CREATE TABLE query result:

CREATE_TABLE `page_revisions` (
 `id` varchar(26) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
 `page_id` varchar(26) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `parent_id` varchar(26) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
 `routable` tinyint(1) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `IDX1` (`page_id`),
 KEY `IDX2` (`parent_id`),
 CONSTRAINT `FK1` FOREING KEY (`parent_id`) REFERENCES `page_revisions` (`id`),
 CONSTRAINT `FK2` FOREING KEY (`page_id`) REFERENCES `pages` (`id`)
) ENGINE=InnoDB

Answer 1

(not an Answer; won't fit in a Comment)

Works for me:

mysql> EXPLAIN
    -> WITH RECURSIVE page_revisions_path (id, page_id, parent_id) AS
    -> (
    ->  SELECT id, page_id, parent_id
    ->  FROM page_revisions
    ->  WHERE parent_id = 'some_id'
    ->  UNION ALL
    ->  SELECT c.id, c.page_id, c.parent_id 
    ->  FROM page_revisions_path AS cp JOIN page_revisions AS c ON cp.id = c.parent_id
    -> )
    -> 
    -> SELECT * FROM page_revisions_path;
+----+-------------+----------------+------------+------+---------------+------+---------+-------+------+----------+--------------------------------------------+
| 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     | page_revisions | NULL       | ref  | IDX2          | IDX2 | 107     | const |    1 |   100.00 | NULL                                       |
|  3 | UNION       | cp             | NULL       | ALL  | NULL          | NULL | NULL    | NULL  |    2 |   100.00 | Recursive                                  |
|  3 | UNION       | c              | NULL       | ALL  | IDX2          | NULL | NULL    | NULL  |    1 |   100.00 | Using where; Using join buffer (hash join) |
+----+-------------+----------------+------------+------+---------------+------+---------+-------+------+----------+--------------------------------------------+
4 rows in set, 1 warning (0.00 sec)

It does require MySQL 8.0 or MariaDB 10.2.

VARCHAR(26) -- Eh? Too short for full names or uuids, etc.

Are there a million rows in the table? How often do you need to run the query?