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 worlds largest e-mail service with MSN Hotmail.
http://www.hotmail.com