Recursion on categorylinks

SQL

set max_recursive_iterations=10;

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(*) from category where 1=1
and cat_title rlike 'list$' collate utf8_general_ci
-- and cat_title not rlike '[a-z]list[a-z]' collate utf8_general_ci
-- and cat_title rlike 'list[a-z]' collate utf8_general_ci
-- limit 1000;

/*
select count(cat_title) from subcats
where cat_title not rlike 'list|set_index_article|glossar|timeline|family_tree' collate utf8_general_ci;
*/

/*
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 page_id) from subcats
join categorylinks on cl_to = cat_title and cl_type = 'page'
join page on cl_from = page_id and page_namespace = 0
*/
SQL

Checking query status...
