SELECT
  @idx := substring_index(@array, ',', 3) AS child
  , @array := CONCAT( REPLACE(@array, @idx, ''), (
      SELECT IFNULL(GROUP_CONCAT(CONCAT(',',`id`,',') SEPARATOR ''), '')
      FROM `tree`
      WHERE `parent_id` = REPLACE(@idx, ',', '')
  )) AS array
FROM (SELECT @array:=',9,') AS dummy
, `tree`
WHERE LENGTH(@array) > 0
;

SELECT
  ch.`id` AS child, ch.`parent_id` AS parent, ch.`level` AS level
  , @array := CONCAT(@array, CONCAT(',',ch.`id`,',')) AS array
FROM (SELECT @array:=',9,') AS dummy
, `tree1` AS ch  FORCE INDEX (level)
WHERE
  LOCATE(CONCAT(',',ch.`parent_id`,','), @array) > 0
;
--# индекс: `level` (`level`,`parent_id`,`id`) лучше делать единственным и первичным ключом таблицы
--# тогда можно не прибивать гвоздями...
--# В этом случае надо забыть про автоинтремент, требующий собственного отдельного индекса...

SET @branch = ?;
SELECT @exists:=EXISTS (
    SELECT @branch:=Concat_ws(',', @branch, c.`id`) branch
      FROM `catalog` c
      WHERE Find_in_set(c.`parent_id`, @branch)
        AND NOT Find_in_set(c.`id`, @branch)
      ORDER BY `branch` DESC LIMIT 1)
  FROM `catalog`
  WHERE @exists;

# test(in integer search_num) {
BEGIN

SET @C='Special create table for ver.2: with composed primary key';
DROP TABLE IF EXISTS `tree`;
CREATE TABLE IF NOT EXISTS `tree` (
   `level`     int(11)          NOT NULL
  ,`parent_id` int(11) unsigned NOT NULL
  ,`id`        int(11) unsigned NOT NULL # auto_increment
  ,`data`      varchar(255) character set 'utf8' default ''
  ,PRIMARY KEY  (`level`,`parent_id`,`id`)
#  ,UNIQUE KEY `id` (`id`) -- for autoincrement needs!
  ,KEY `parent_id` (`parent_id`)
  ,KEY `id`        (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

SET @C='Inserting tree into table';
SET @depth = 10;
SET @maxLevel = 300;

SET @level = 0;
REPEAT
  SET @d = 1;
  REPEAT
    INSERT INTO `tree` SET
        `level`     = @level
      , `parent_id` = @parent:=IF(@level=0, 0, @d + (@level-1) * @depth) #FLOOR( RAND()*@level*@depth ) )
      , `id`        = @d + @level * @depth
      , `data`      = CONCAT('data for parent = ',@parent)
    ;
    SET @d = @d + 1;
  UNTIL @d > @depth END REPEAT;
  SET @level = @level + 1;
UNTIL @level > @maxLevel END REPEAT;
COMMIT;

SET @C='Ver.1: with subquery for each childs... correct for simple query and any tables but stuppid...';
SELECT
  @idx := CAST(SUBSTRING_INDEX(@array, ',', 1) AS SIGNED) AS idx
  , @array := CONCAT_WS(
          IF(LENGTH(@array) > LENGTH(@idx), ',', '')
          , SUBSTRING(@array, CHAR_LENGTH(@idx)+2)
          , (SELECT GROUP_CONCAT(`id`) FROM `tree` WHERE `parent_id` = @idx)
  ) AS array
FROM (SELECT @array:=CONCAT('',search_num), @idx:=0) AS dummy
, `tree` AS ch
WHERE
  LENGTH(@array) > 0
;
# selected=301 items by 0.59 .. 0.61sec. (10 times).

SET @C='Ver.2: table must composed primary key (level, parent_id, id)... quickly,  full index scan...';
SELECT
  ch.`level` AS LEVEL
  , IF( @pr<>ch.`parent_id`, GREATEST(
        @array:=REPLACE(@array, @prc, '')
        , @prc:=CONCAT(',',ch.`parent_id`,',')
        , @pr:=ch.`parent_id`
    ), @pr) AS parent
  , GREATEST(ch.`id`
    , IF( LOCATE(@prc, @array) > 0
        , @array := CONCAT(@array, CONCAT(',',ch.`id`,','))
        , ''
  )) AS childs
FROM (SELECT @prc:=@array:=CONCAT(',',@pr:=search_num,',')) AS dummy
, `tree` AS ch FORCE INDEX (`PRIMARY`) # if present other index!
WHERE LOCATE( CONCAT(',',ch.`parent_id`,','), @array) > 0;
# selected=300 items by 0.21 .. 0.8 sec.


SET @branch=CONCAT('', search_num);
SELECT @branch as branch
  FROM `tree`
  WHERE EXISTS (SELECT 
         @branch:=CONCAT_WS(',', @branch, GROUP_CONCAT(`id`)) branch
       FROM `tree`
       WHERE FIND_IN_SET(`parent_id`, @branch)
         AND NOT FIND_IN_SET(`id`, @branch)
       GROUP BY `parent_id`
       ORDER BY `branch` DESC LIMIT 1)
  ORDER BY `branch` DESC LIMIT 1;
#selected=string by 2min 15sec. (one time starting index was added)


SET @branch = CONCAT('', search_num);
SELECT @exists:=EXISTS (
    SELECT @branch:=Concat_ws(',', @branch, c.`id`) branch
      FROM `tree` c
      WHERE Find_in_set(c.`parent_id`, @branch)
        AND NOT Find_in_set(c.`id`, @branch)
      ORDER BY `branch` DESC LIMIT 1)
  FROM `tree`
  WHERE @exists;
SELECT @branch;
# selected by 0.046sec, but only 1 result: @branch = '3' (one result only!);


SET @branch=CONCAT('', search_num);
SET @exists = 1;
SELECT `id`
  FROM `tree`, (
    SELECT @branch as branch, @exists:=EXISTS (
      SELECT @branch:=Concat_ws(',', @branch, c.`id`) branch
        FROM `tree` c
        WHERE Find_in_set(c.`parent_id`, @branch)
          AND NOT Find_in_set(c.`id`, @branch)
        ORDER BY `branch` DESC LIMIT 1) rowsExists
    FROM `tree`
    WHERE @exists
    ORDER BY `branch` DESC LIMIT 1) as branch
  WHERE Find_in_set(`id`, `branch`.`branch`)
;
# selected=301 items by 0.398sec (three time starting)

END

SET @C='Ver.2: table must composed primary key (level, parent_id, id)... quickly,  full index scan...';
SELECT
  ch.`level` AS LEVEL
  , IF( @pr<>ch.`parent_id`, GREATEST(
        @array:=REPLACE(@array, @prc, '')
        , @prc:=CONCAT(',',ch.`parent_id`,',')
        , @pr:=ch.`parent_id`
    ), @pr) AS parent
  , GREATEST(ch.`id`
    , IF( LOCATE(@prc, @array) > 0
        , @array := CONCAT(@array, CONCAT(',',ch.`id`,','))
        , ''
  )) AS childs
FROM (SELECT @prc:=@array:=CONCAT(',',@pr:=search_num,',')) AS dummy
, `tree` AS ch FORCE INDEX (`PRIMARY`) # if present other index!
WHERE LOCATE( CONCAT(',',ch.`parent_id`,','), @array) > 0;
# selected=300items by 0.021 .. 0.080 sec.