Most frequent dead external links in articles on plwiki

SQL

SELECT externallinks.el_to, COUNT(*) AS counting
FROM (SELECT SUBSTRING_INDEX(el_to + 3, '/', 1) AS el_to, COUNT(*) AS counting
FROM page
JOIN externallinks
ON page.page_id = externallinks.el_from
WHERE el_to NOT LIKE 'http://web.archive.org/%'
AND el_to NOT LIKE 'http://toolserver.org/%'
AND page_title LIKE 'MastiBot/Statystyka_martwych_linków/ogólne'
AND page_namespace = 2
AND page_id = el_from
GROUP BY el_to) tmp, page
JOIN externallinks
ON page.page_id = externallinks.el_from
WHERE page_namespace = 0
AND tmp.el_to = externallinks.el_to
GROUP BY externallinks.el_to
HAVING counting >= 2
ORDER BY counting DESC, el_to;




/*SELECT el_to, COUNT(*) AS counting
FROM (SELECT SUBSTRING_INDEX(SUBSTRING(el_to, LOCATE('://', el_to) + 3), '/', 1) AS el_to
FROM externallinks
WHERE el_from IN (SELECT page_id
FROM page
WHERE page_namespace = 1)
) AS tmp
WHERE el_to NOT LIKE 'web.archive.org'
AND el_to NOT LIKE 'toolserver.org'
AND el_to NOT LIKE '%wikipedia.%'
GROUP BY el_to
HAVING counting >= 12
ORDER BY counting DESC, el_to;*/
SQL

Checking query status...
