Top TLDs used in external links (eswiki, main namespace) v1

This query shows TLDs used in external links from the main namespace of Spanish Wikipedia, along with a count of how many links they appear in, as well as the number of articles containing, at least, one link with the given TLD.

Only links with scheme http, https, ftp or relative (//) are considered. That excludes other such as irc://, mailto: or svn://.

WARNING: This query does not support IDN TLDs.

SQL

USE eswiki_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 USE INDEX (`el_index`)
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...