I’m having a huge performance issue when I run recursive query. Here is my DB Structure:

users:
| id | name |
| — | —- |
| 1 | A |
| 2 | B |
| 3 | C |
| 4 | D |
| 5 | E |

user_pivot
| user_id | parent_id |
| ——- | ——— |
| 2 | 1 |
| 3 | 2 |
| 4 | 2 |
| 5 | 4 |

And this is the illustration of my tree structure:

As you may realized, all users in level 3 belongs to every single user in level 2 (as same as level 5 users which belongs to every single user in level 4). The problem with this structure is that when I want to get all children which belong to user “A” a huge redundancy occurs.

Here is my query:

WITH recursive `cte` AS ((
    SELECT
      `users`.id,
      1 AS `depth`,
      cast(
      `users`.`id` AS CHAR ( 65535 )) AS `path`,
      `user_pivot`.`parent_id` AS `pivot_parent_id`,
      `user_pivot`.`user_id` AS `pivot_user_id` 
    FROM
      `users`
      INNER JOIN `user_pivot` ON `users`.`id` = `user_pivot`.`user_id` 
    WHERE
      `user_pivot`.`parent_id` = 4) UNION ALL
    (
    SELECT
      `users`.id,
      `depth` + 1 AS `depth`,
      concat( `path`, 4, `users`.`id` ),
      `user_pivot`.`parent_id` AS `pivot_parent_id`,
      `user_pivot`.`user_id` AS `pivot_user_id` 
    FROM
      `users`
      INNER JOIN `user_pivot` ON `users`.`id` = `user_pivot`.`user_id`
      INNER JOIN `laravel_cte` ON `laravel_cte`.`id` = `user_pivot`.`parent_id` 
    )) SELECT DISTINCT
  `id` 
FROM
  `cte`

Any suggestion to optimize the query?