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