gistfile1.sql

EXPLAIN SELECT root_node.id, GROUP_CONCAT(children.id) as child_ids
FROM contents as root_node 
RIGHT JOIN contents as children ON root_node.id = children.parent_id AND children.parent_id = root_node.id AND children.content_type_id = 6 
WHERE root_node.content_type_id = 2
GROUP BY root_node.id;

+----+-------------+-----------+--------+---------------+---------+---------+------------------------------------------+-------+----------------------------------------------+
| id | select_type | table     | type   | possible_keys | key     | key_len | ref                                      | rows  | Extra                                        |
+----+-------------+-----------+--------+---------------+---------+---------+------------------------------------------+-------+----------------------------------------------+
|  1 | SIMPLE      | children  | ALL    | NULL          | NULL    | NULL    | NULL                                     | 12354 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | root_node | eq_ref | PRIMARY       | PRIMARY | 4       | yarpaultv_development.children.parent_id |     1 | Using where                                  | 
+----+-------------+-----------+--------+---------------+---------+---------+------------------------------------------+-------+----------------------------------------------+