Topic on Talk:Quarry

I'm trying to get a list of categories which belong to only hidden categories. In other words, when I go to the category page, all the categories listed at the bottom are hidden categories, none of which are normal categories. I manage to get the logic out, but I think the query is not efficient and cannot run in time before being stopped. Wondering if anyone could help with optimizing the query? Thank you!

(zhwiki_p)

SELECT p.page_title
FROM page p 
INNER JOIN categorylinks cl 
	ON p.page_id = cl.cl_from
LEFT JOIN (
	-- 11k list of hidden categories
	SELECT p2.page_title, pp.pp_propname
	FROM page_props pp 
	INNER JOIN page p2 ON (p2.page_id = pp.pp_page)
	WHERE pp.pp_propname = 'hiddencat'
) hc 
	ON cl.cl_to = hc.page_title
WHERE p.page_namespace = 14 -- 400k
GROUP BY p.page_title
HAVING COUNT(*) = SUM(CASE WHEN hc.pp_propname IS NULL THEN 0 ELSE 1 END)

You could try something like this (Quarry):

SELECT page_title, page_latest
FROM page AS pages
WHERE pages.page_namespace = 14
AND pages.page_is_redirect = 0
AND NOT EXISTS (
  SELECT 1 FROM categorylinks
  LEFT JOIN page AS categories ON categories.page_title = cl_to AND categories.page_namespace = 14
  LEFT JOIN page_props ON pp_page = categories.page_id AND pp_propname = 'hiddencat'
  WHERE cl_from = pages.page_id AND (pp_propname IS NULL OR categories.page_title IS NULL)
);

Thank you for your help, this query works! I am not familiar with EXISTS and NOT EXISTS in SQL, will read up more on this.