nlwiki Red article links w/ rare title suffixes between parenthesis

(just suffixes w/ lowercase letters for now)
1. count all suffixes in article links
2. get red article links with a suffix
3. join and select rare suffixes (currently: that occur only once)

takes 15-20 mins

https://nl.wikipedia.org/wiki/Gebruiker:Bdijkstra/quarry/Haakjesanomalieën

SQL

SELECT s.`suffix`, CONCAT('[[:',`from`,']]'), CONCAT('[[:',`to`,']]')
FROM (
	SELECT `suffix`, COUNT(*) AS `count`
	FROM (SELECT pl_from, pl_title, REGEXP_REPLACE(pl_title, '^.*_\\(([^\\(\\)]+)\\)$', '\\1') AS `suffix`
		  FROM pagelinks
          WHERE pl_from_namespace=0 AND pl_namespace=0 AND pl_title REGEXP '_\\([\\p{Ll}_]+\\)$') pls
	GROUP BY `suffix`
	HAVING `count`=1
) s, (
	SELECT page_title AS `from`, GROUP_CONCAT(pl_title) AS `to`, `suffix`
	FROM (SELECT pl_from, pl_title, REGEXP_REPLACE(pl_title, '^.*_\\(([^\\(\\)]+)\\)$', '\\1') AS `suffix`
		  FROM pagelinks
          WHERE pl_from_namespace=0 AND pl_namespace=0 AND pl_title REGEXP '_\\([\\p{Ll}_]+\\)$'
         ) pls INNER JOIN page ON pl_from=page_id
	WHERE
		NOT EXISTS (SELECT * FROM page WHERE pl_title=page_title AND page_namespace=0)
	GROUP BY `suffix`
) r
WHERE s.suffix=r.suffix
LIMIT 5000
/*SELECT `suffix`, COUNT(*) AS `count`, page_title AS `from`, GROUP_CONCAT(pl_title) AS `to`
FROM (
	SELECT page_title, pl_title, REGEXP_REPLACE(pl_title, '^.*_\\(([^\\(\\)]+)\\)$', '\\1') AS `suffix`
	FROM pagelinks INNER JOIN page ON pl_from=page_id
	WHERE
		NOT EXISTS (SELECT * FROM page WHERE pl_title=page_title AND page_namespace=0) AND
		pl_from_namespace=0 AND pl_namespace=0 AND pl_title REGEXP '_\\([\\p{Ll}_]+\\)$'
	#LIMIT 1000
) t
GROUP BY `suffix`
HAVING `count`=1
LIMIT 500*/

SQL

Checking query status...