SET @basecat='Mathematics';

SET max_recursive_iterations=7;

​

WITH RECURSIVE deepcat (subcat, catpath, depth) AS

(

  SELECT REPLACE(@basecat, '_', ' '), REPLACE(@basecat, '_', ' '), 0

​

  UNION

​

  SELECT page_title, CONCAT(catpath, ' > ', REPLACE(page_title, '_', ' ')), depth + 1

  FROM categorylinks

  JOIN page ON page_id = cl_from AND page_namespace = 14

  JOIN deepcat ON cl_to = subcat

)

SELECT DISTINCT REPLACE(page_title, '_', ' ') AS title,

       MIN(depth),

       MIN(catpath) OVER (PARTITION BY page_title ORDER BY depth ASC) AS 'category path'

FROM page

JOIN categorylinks ON cl_from = page_id

JOIN deepcat ON cl_to = subcat

WHERE page_namespace = 0

  AND page_is_redirect = 0

  AND LOWER(CONVERT(page_title USING 'utf8')) REGEXP '_dog'

GROUP BY page_title

ORDER BY MIN(depth) ASC;