Category ancestors cycles

SQL

/*
select count(*) from category where cat_subcats = 0;

select count(distinct cl_to) from categorylinks
join page on cl_from = page_id and page_namespace = 14
where page_title in (select cat_title from category where cat_subcats = 0);
*/

/*
with category_2 as (
  select distinct cl_to from categorylinks
  join page on cl_from = page_id and page_namespace = 14
  where page_title in (select cat_title from category where cat_subcats = 0)
),
category_3 as (
  select distinct cl_to from categorylinks
  join page on cl_from = page_id and page_namespace = 14
  where page_title in (select cl_to from category_2)
)

select count(cat_title) from category where cat_subcats > 0
and cat_title not in (select cl_to from category_2)
and cat_title not in (select cl_to from category_3)
*/

/*
select page_title, category.* from categorylinks
join page on cl_from = page_id and page_namespace = 14
join category on cat_title = cl_to and cat_subcats = 0
-- where cl_to in (select cat_title from category where cat_subcats = 0)
*/

/*
select count(*) from categorylinks
join page on cl_from = page_id and page_namespace = 14;
*/

set max_recursive_iterations=3;

with recursive ancestors (an_from, an_to, path, cicle) as (
  select cat_title, cl_to, CONCAT(cat_title, ',', cl_to), false from subcategories
  join (select cat_title from category where cat_subcats = 0 limit 100) category on page_title = cat_title -- and cat_subcats = 0
  union all
  select an_to, cl_to, CONCAT(path, ',', cl_to), FIND_IN_SET(cl_to, path) from subcategories
  join ancestors on page_title = an_to
  where not cicle
),
subcategories as (
  select page_title, cl_to from categorylinks
  join page on cl_from = page_id and page_namespace = 14
)

select count(*) from ancestors where cicle;

/*
with recursive ancestors (an_from, an_to, path) as (
  select cat_title, cl_to, CONCAT(cat_title, ',', cl_to) from subcategories
  join category on page_title = cat_title and cat_subcats = 0
  union distinct
  select an_to, cl_to, CONCAT(an_to, ',', cl_to) from subcategories
  join ancestors on page_title = an_to
  union distinct
  select an_from, cl_to, CONCAT(path, ',', cl_to) from subcategories
  join ancestors on page_title = an_to
),
subcategories as (
  select page_title, cl_to from categorylinks
  join page on cl_from = page_id and page_namespace = 14
  limit 100
)

select * from ancestors;
*/
SQL

Checking query status...
