Toolserver:User:LA2

LA2 is the username of Lars Aronsson, Sweden.

November 25, 2011: The externallinks table has columns (el_from int, el_to blob, el_index blob), where el_to is the full external URL and el_index is the URL with the domain name in reverse. Many websites are identified by the last two components of the domain name, but for bbc.co.uk, three components are necessary. el_from refers to page_id in page, where page_namespace is 0 for articles. To get the full domain name, just stripping any initial "www.", use:

select count(*), trim(leading 'www.' from substring_index(substring_index(el_to, '/', 3),'/',-1)) as site from externallinks, page where el_from = page_id and page_namespace=0 group by 2 having count(*) > 10 order by 1;

On svwiki_p this query takes 10-40 seconds.

en:User:LA2 sv:Användare:LA2