Recursion on categorylinks

SQL

set max_recursive_iterations=3;

with recursive subcats (cat_title) as (
  select cl_to from categorylinks where cl_to in ('Lists')
  -- union all
  union distinct
  select page_title from subcats
  join categorylinks on cl_to = cat_title and cl_type = 'subcat'
  join page on cl_from = page_id
)
-- cycle cat_title restrict
-- select count(cat_title) from subcats;
-- select min(depth), max(depth), cat_title, count(*) from subcats group by cat_title order by count(depth) desc limit 10;

/*
select count(distinct cl_from) from subcats
join categorylinks on cl_to = cat_title and cl_type != 'subcat'
-- where cat_title not in (select cat_title from category where cat_pages = 0)
-- join page on cl_from = page_id
-- order by page_namespace;
*/


select count(distinct p1.page_id) from subcats
join categorylinks on cl_to = cat_title and cl_type = 'page'
join page p1 on cl_from = p1.page_id and p1.page_namespace = 0
join page p2 on p2.page_title = cat_title and p2.page_namespace = 14 and p2.page_title not like '%list%' collate utf8_general_ci

/*
SELECT p1.page_title, pl_title, COUNT(pl_title) as links
FROM page p1 LEFT JOIN pagelinks ON pl_from = p1.page_id AND pl_namespace = 0
AND pl_title IN (select p2.page_title from page p2 where p2.page_namespace = 0)
-- LEFT JOIN page p2 ON p2.page_title = pl_title AND p2.page_namespace = 0
-- join categorylinks on cl_from = p1.page_id and cl_to in ("All_disambiguation_pages", "All_set_index_articles")
join categorylinks on cl_type = 'page' and cl_from = p1.page_id
join subcats on cl_to = cat_title
-- WHERE p1.page_id IN (select cl_from from categorylinks where cl_to in ("All_disambiguation_pages", "All_set_index_articles"))
WHERE p1.page_namespace = 0 AND p1.page_is_redirect = 0
GROUP BY p1.page_title
HAVING p1.page_title LIKE '%\_(disambiguation)' AND links < 2 OR links < 1
ORDER BY links
*/
SQL

Checking query status...
