Redirected portals with existing subpages

SQL

select p1.page_title, rd_namespace, rd_title, count(p2.page_title), count(p3.page_title) from page p1
join redirect on rd_from = p1.page_id
left join page p2 on concat(p1.page_title, '/') = substring(p2.page_title, 1, length(p1.page_title)+1)
and p2.page_namespace = 100 and p2.page_is_redirect = 0
left join page p3 on concat(p1.page_title, '/') = substring(p3.page_title, 1, length(p1.page_title)+1)
and p3.page_namespace = 101 and p3.page_is_redirect = 0
where p1.page_namespace = 100 and p1.page_id in (
  select cl_from from categorylinks
  where cl_to in ("Redirected_portals_with_existing_subpages") and cl_type = 'page'
)
group by p1.page_title order by count(p2.page_title) desc, count(p3.page_title) desc
SQL

Checking query status...
