> In order to quickly scan data of all children, I want to also have a column of each child pointing to the RootId node.
Given table familytree(parentID INT,childID INT), what does your extra column contribute to this query ...
SELECT t.parentID AS Parent, GROUP_CONCAT(f.childID) AS Children
FROM familytree t
JOIN familytree f ON t.parentID=f.childID
GROUP BY t.parentID;
or to this subtree sproc?
DROP PROCEDURE IF EXISTS famsubtree;
DELIMITER go
CREATE PROCEDURE famsubtree( root INT )
BEGIN
DROP TABLE IF EXISTS famsubtree;
CREATE TABLE famsubtree
SELECT childID, parentID, 0 AS level
FROM familytree
WHERE parentID = root;
ALTER TABLE famsubtree ADD PRIMARY KEY(childID,parentID);
REPEAT
INSERT IGNORE INTO famsubtree
SELECT f.childID, f.parentID, s.level+1
FROM familytree AS f
JOIN famsubtree AS s ON f.parentID = s.childID;
UNTIL Row_Count() = 0 END REPEAT;
END ;
PB