Untitled query #25050

SQL

USE euwiki_p;
SELECT
	REGEXP_REPLACE(SUBSTRING(el_index FROM 1 FOR 60), '(?i)^(?:https?:|ftp:)?//([a-z]+)\\..*?$', '\\1') AS tld,
    COUNT(DISTINCT page_id) 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 '(?i)^(?:https?:|ftp:)?//(?:[a-z]+)\\..*?$'
GROUP BY tld
ORDER BY article_count DESC, total_count DESC;
SQL

Checking query status...
