Wikimedia Bugzilla is closed!

This issue was converted from https://jira.toolserver.org/browse/DBQ-72.
Summary: distinct articles per user
Issue type: Task - A task that needs to be done.
Priority: Major
Status: Done
Assignee: (none)

-------------------------------------------------------------------------------
From: Nuno Tavares <nunotavares@hotmail.com>
Date: Wed, 19 Aug 2009 02:48:41
-------------------------------------------------------------------------------

We are trying to provide some statistics for studying. I've reached a query that may be used (by us) for that analysis, but it's pretty heavy. 

SELECT a.rev_user_text,p.page_namespace,COUNT(a.rev_page) AS artigos_distintos  
FROM (SELECT r.rev_user,r.rev_page,r.rev_user_text  
FROM wikidb_revision r   
GROUP BY r.rev_user,r.rev_page) AS a  
JOIN wikidb_user u ON u.user_id = a.rev_user  
JOIN wikidb_page p ON a.rev_page = p.page_id  
GROUP BY rev_user,p.page_namespace

Can this be run in the Query service? I've made some tests, and it seems it's better to use an external table, like this:

create table teste select r.rev_user, p.page_namespace, r.rev_page, count(1) AS edits  
from revision r  
JOIN page p ON r.rev_page = p.page_id  
GROUP BY r.rev_user,p.page_namespace,r.rev_page;

alter table teste add key idx_u (rev_user,page_namespace);

select straight_join u.user_name, page_namespace,count(1) as edits from teste  
join user u on u.user_id = rev_user  
group by rev_user,page_namespace;

Hope that helps.