Templates with link and redlink counts

SQL

SELECT Pt.page_title, COUNT(*) links, SUM(CASE WHEN Pa.page_title IS NULL THEN 1 ELSE 0 END) redlinks,
 (SELECT GROUP_CONCAT(cl_to) FROM categorylinks WHERE cl_from = pl_from AND cl_type='page') AS categories
FROM pagelinks
/* JOIN page Pt ON Pt.page_id = pl_from */
LEFT JOIN page Pa ON Pa.page_title = pl_title AND Pa.page_namespace = 0
JOIN page Pt ON Pt.page_id = pl_from AND Pt.page_title NOT REGEXP "/(doc|sandbox|testcases)\\b"
WHERE pl_from_namespace = 10 AND pl_namespace = 0 /* Template→Article links only */
GROUP BY Pt.page_title WITH ROLLUP
HAVING links >= 100 AND redlinks BETWEEN 1 AND 5

SQL

Checking query status...