Categories who are under hidden categories only (zhwiki_p)

SQL

/* SELECT p.page_title, (CASE WHEN hc.pp_propname IS NOT NULL THEN 1 ELSE 0 END) AS hidden_cat
FROM page 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
WHERE page_namespace = 14 -- 400k
LIMIT 100
*/
SELECT p.page_id, p.page_title, cl.cl_to FROM page p
INNER JOIN categorylinks cl ON cl.cl_from = p.page_id
INNER JOIN page p2 ON cl.cl_to = p2.page_title AND p2.page_namespace = 14 # 14 = Category
LEFT JOIN page_props pp ON pp.pp_page = p2.page_id AND pp.pp_propname = 'hiddencat'
WHERE pp.pp_page IS NOT NULL
LIMIT 10

SQL

Checking query status...