From: Daniel Kinzler <daniel@brightbyte.de>

Date: Wed, 31 Oct 2007 13:13:35

sadly, finding the first revision for a given page is rather expensive. to get what you want, this would have to be done for every page, and the result then grouped by user. I can think of an efficient way to do that.

From: Bryan Tong Minh <bryan@tools.wikimedia.de>

Date: Thu, 01 Nov 2007 22:16:34

I would come up with something like this:

SELECT lr_user, COUNT(lr_title) FROM (SELECT page_title AS lr_title, MIN(rev_id) AS lr_rev_id, rev_user_text AS lr_user FROM page, revision WHERE page_namespace = 0 AND rev_page = page_id GROUP BY rev_page) AS lowest_revision GROUP BY lr_user;

Unfortunately, that does not look like it will run very efficiently. Even an EXPLAIN takes ages.

From: Bryan Tong Minh <bryan@tools.wikimedia.de>

Date: Mon, 05 Nov 2007 09:20:49

Final query:

mysql -hsql-s1 enwiki_p -e "SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT lr_user, COUNT(lr_title) AS lr_creation_count FROM (SELECT page_title AS lr_title, MIN(rev_id) AS lr_rev_id, rev_user_text AS lr_user FROM page, revision WHERE page_namespace = 0 AND page_is_redirect = 0 AND rev_page = page_id GROUP BY rev_page) AS lowest_revision GROUP BY lr_user HAVING lr_creation_count > 5 ORDER BY lr_creation_count DESC;" >enwiki_page_creation.txt

Approximate runtime: 30h

Results: http://tools.wikimedia.de/~bryan/stats/dbquery/enwiki_page_creation.txt

From: Dutchresearch <mail@basvaneijk.nl>

Date: Tue, 03 Mar 2009 15:20:30

Hi i am new to the TS and i would like to have similar info.

Could anyone help me get results for

member name | total started articles per user | total edits on articles per user | registration date | Last active date

I will use it for my thesis on UGC which i will publish CC

i runned the SQL above on the sco.wiki took me 10s. ![][1]

but i want to adjust this SQL to get the info above

Bas

SELECT lr_user, COUNT(lr_title) AS lr_creation_count
FROM
(SELECT
page_title AS lr_title,
MIN(rev_id) AS lr_rev_id,
rev_user_text AS lr_user
FROM page, revision
WHERE page_namespace = 0
AND page_is_redirect = 0
AND rev_page = page_id
GROUP BY rev_page) AS lowest_revision
GROUP BY lr_user
HAVING lr_creation_count > 1
ORDER BY lr_creation_count DESC

[1]: https://jira.toolserver.org/images/icons/emoticons/smile.gif

This bug was imported as RESOLVED. The original assignee has therefore not been
set, and the original reporters/responders have not been added as CC, to
prevent bugspam.

If you re-open this bug, please consider adding these people to the CC list:
Original assignee: Bryan.TongMinh@Gmail.com
CC list: Bryan.TongMinh@Gmail.com, daniel.kinzler@wikimedia.de