EXPLAIN EXTENDED

In this article, we will find the leaves of the hierarchy tree.

A row is a leaf in a hierarchy tree if it has no children.

In Oracle, there is a speical pseudocolumn that tests if a given row is a leaf in a CONNECT BY query. Surprisingly, it's called CONNECT_BY_ISLEAF.

This pseudocolumn is quite straightforward: it returns 1 when a row is a leaf, 0 otherwise.

This pseudocolumn is quite easy to emulate in MySQL, and we can do it even without using user-defined functions:

SELECT  CONCAT(REPEAT('    ', lvl - 1), hi.id) AS treeitem,
hierarchy_sys_connect_by_path('/', hi.id) AS path,
parent, lvl,
CASE
WHEN lvl >= @maxlevel THEN 1
ELSE COALESCE(
(
SELECT  0
FROM    t_hierarchy hl
WHERE   hl.parent = ho.id
LIMIT 1
), 1)
END AS is_leaf
FROM    (
SELECT  hierarchy_connect_by_parent_eq_prior_id_with_level(id, @maxlevel) AS id,
CAST(@level AS SIGNED) AS lvl
FROM    (
SELECT  @start_with := 1,
@id := @start_with,
@level := 0,
@maxlevel := 2
) vars, t_hierarchy
WHERE   @id IS NOT NULL
) ho
JOIN    t_hierarchy hi
ON      hi.id = ho.id

This expression works as follows:

1. It tests if the current row level hits the maximum level constraint. If it does, the expression returns 1 immediately, as even if there are children in the table itself, their level would exceed the maximum level value and, therefore, they would not be returned by hierarchy_connect_by_parent_eq_prior_id_with_level
2. If the level is OK, the expression selects 0 for the first child of the current row. If there is no child, then the row is a leaf, and the expression returns 1

This is an efficient way to do the check, as the subquery performs just a quick one row index scan without costly function calls.

To be continued.