Untitled query #4740

SQL

USE cswiki_p;
#SHOW TABLES;
#DESCRIBE imagelinks;
SELECT page_title, page_len, interwiki, anon_users, anon_edits, liu_users, liu_edits,
	COUNT(DISTINCT images.il_to) AS image, GROUP_CONCAT(DISTINCT portals.pl_title SEPARATOR ', ') AS portal,
	GROUP_CONCAT(DISTINCT ib.tl_title SEPARATOR ', ') AS ibox, GROUP_CONCAT(DISTINCT nb.tl_title SEPARATOR ', ') AS navbox,
	GROUP_CONCAT(DISTINCT issues.tl_title SEPARATOR ', ') AS issues
	FROM (SELECT * FROM categorylinks WHERE cl_to = "Čeští_spisovatelé") AS category
	JOIN (SELECT * FROM page WHERE page_namespace = 0) AS pages
		ON cl_from = page_id
	LEFT JOIN (SELECT ll_from, COUNT(*) AS interwiki FROM langlinks GROUP BY ll_from) AS inter
		ON ll_from = page_id
	LEFT JOIN (
		SELECT rev_page, COUNT(DISTINCT rev_user_text) AS anon_users, COUNT(*) AS anon_edits
		FROM revision WHERE rev_user = 0
		GROUP BY rev_page
	) AS anon
		ON anon.rev_page = page_id
	LEFT JOIN (
		SELECT rev_page, COUNT(DISTINCT rev_user_text) AS liu_users, COUNT(*) AS liu_edits
		FROM revision WHERE rev_user > 0
		GROUP BY rev_page
	) AS liu
		ON liu.rev_page = page_id
	LEFT JOIN pagelinks AS portals
		ON portals.pl_from = page_id AND portals.pl_namespace = 100
	LEFT JOIN imagelinks AS images
		ON images.il_from = page_id
	LEFT JOIN templatelinks AS ib
		ON ib.tl_from = page_id AND ib.tl_namespace = 10 AND ib.tl_title RLIKE "^Infobox_[-A-Z]"
	LEFT JOIN templatelinks AS nb
		ON nb.tl_from = page_id AND nb.tl_namespace = 10 AND nb.tl_title IN (
			SELECT page_title FROM templatelinks
			JOIN page ON page_id = tl_from
          	WHERE tl_title = "Navbox" AND tl_namespace = 10 AND tl_from_namespace = 10
		) AND nb.tl_title NOT IN ("Navbox")
	LEFT JOIN templatelinks AS issues
		ON issues.tl_from = page_id AND issues.tl_namespace = 10 AND issues.tl_title IN (
			SELECT page_title FROM categorylinks
			JOIN page ON page_id = cl_from
          	WHERE cl_to = "Šablony_pro_údržbu_článků" AND page_namespace = 10
		)
GROUP BY page_id
ORDER BY page_len DESC, page_title
LIMIT 100;
SQL

Checking query status...
