Optimise MySQL SELECT with LEFT JOIN subquery
The main reason for the slow query is the join on a subquery. This will not use indexes. Then, you not only join with a derived table (subquery), but as well group total result based on subquery column - GROUP BY l.id, s.Siblings

In this case it could help to:


create temporary table from subquery, it also could include subquery
for return correct parent_id
create index on this table
use temporary table in join
drop temporary table


This could have variants, but it is often faster and less server-loading than a complicated set of subqueries with joins.