Categories who are under hidden categories only (zhwiki_p)

SQL

/* SELECT p.page_title, COUNT(*) AS cat_count
FROM (
    -- 400k
	SELECT page_id, page_title
    FROM page
    WHERE page_namespace = 14
) p 
INNER JOIN categorylinks cl 
	ON p.page_id = cl.cl_from
LEFT JOIN (
    -- 11k
	SELECT p2.page_title, pp.pp_propname
	FROM page_props pp 
	LEFT JOIN page p2 ON (p2.page_id = pp.pp_page)
	WHERE pp.pp_propname = 'hiddencat'
) hc 
	ON cl.cl_to = hc.page_title
LIMIT 10
*/
SELECT
	page_id,
	page_title,
    page_len
FROM page ON cat_title = page_title
LEFT JOIN categorylinks ON page_id = cl_from
WHERE
	cl_from IS NULL
    AND page_namespace = 14

SQL

Checking query status...