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