[Wikipedia-l] Using database queries

[Tim Marklew]

LDC wrote:
>I'd like to point out that every sysop here (and that's over
>15% of active users, as someone pointed out) can make SELECT
>queries to the database, which is all that's necessary for
>getting any kind of information you need.  I'd like to
>encourage more users to learn how to do this, and encourage
>those who already do know how to help them.
This feature is useful - I've been using it to produce pages like 
[[Wikipedia:Defective redirects]] and [[Wikipedia:Disambiguation pages with 
links]] that show up faulty links, as well as some of the stats I've been 
posting.

LDC - could you comment on how potentially dangerous this function is in 
terms of slowing down the site for others?  If a query results in a big 
table, this can slow down the server.  Is there an upper limit for how long 
a query can take, or the size of a table, before it would time out?  If so, 
how big is the limit?

In the meantime, here are some ideas for database queries that I have found 
useful.  Sysops can use by pasting them directly into the query box.  When 
they seem to be taking a significant amount of time to run, use them 
sparingly to avoid slowing things down too much for other users.  If you 
don't understand how they work, feel free to use them, but I would suggest 
using them 'as is' without changing them to avoid accidently slowing things 
down seriously.

Here's a couple of queries to find the 20 most newly created users - useful 
to find people to give welcome messages.  Kajakit recently requested 
something like this.  It only counts users that have made edits (most users 
who create a login never make a single contribution and are just passing by, 
so it's not really worth welcoming them).

SELECT user_name, COUNT(*) FROM user, cur WHERE user_name=cur_user_text 
GROUP BY user_id ORDER BY user_id DESC LIMIT 20

The above query gives the user names of the 20 most recently created users 
and the number of edits they have made.  The query is not perfect - only 
edits that have not been 'overwritten' by other edits are counted here.  To 
do the same thing looking only at 'overwritten' edits use:

SELECT user_name, COUNT(*) FROM user, old WHERE user_name=old_user_text 
GROUP BY user_id ORDER BY user_id DESC LIMIT 20

And here's the query that I used to create the page 
[[Wikipedia:Disambiguation pages with links]]:

SELECT cur1.cur_title, COUNT(*) as co FROM cur cur1, links links1, links 
links2 WHERE links2.L_from<>"Wikipedia:Links_to_disambiguating_pages" AND 
links2.l_to = cur1.cur_id AND 
links1.l_from="Wikipedia:Links_to_disambiguating_pages" AND links1.l_to = 
cur1.cur_id GROUP BY cur1.cur_title ORDER BY co DESC LIMIT 100

Someone also asked about how the page [[Wikipedia:Defective redirects]] was 
produced - the method of producing that page was a bit complicated so I 
won't give it here.  I will however be reproducing that page every now and 
then to help identify and fix redirects-to-redirects.

I hope this is useful.  Let me know if anyone has any more requests for 
information like this that may be obtained from the database.

Tim (Enchanter)


_________________________________________________________________
Join the world’s largest e-mail service with MSN Hotmail. 
http://www.hotmail.com