Untitled query #25050

SQL

USE euwiki_p;
SELECT
	SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTRING(el_index FROM 1 FOR 60), '.', 1), '/', -1) AS tld,
    COUNT(DISTINCT el_from) AS article_count,
    COUNT(*) AS total_count
FROM externallinks
WHERE
	-- Only links from main namespace articles.
    el_from_namespace = 0
    -- Only http, https, implicit https (//) and ftp. el_index is already lowercase.
    AND (el_index LIKE 'http://%' OR el_index LIKE 'https://%' OR el_index LIKE '//%' OR el_index LIKE 'ftp://%')
    -- Get only fairly well-formed URLs and exclude IPv4.
    AND el_index REGEXP '^(?:https?:|ftp:)?//(?:[a-z]+)\\.[-_0-9a-z]+\\..*?$'
GROUP BY tld
ORDER BY article_count DESC, total_count DESC;
SQL

Checking query status...
