Untitled query #25050

SQL

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

Checking query status...
