Last modified: 2010-05-15 15:33:08 UTC
We have recently moved to MW 1.4 (wiki.ehow.com), we have about 1.25 million
users imported from our site eHow. When we attempt to load the page
Special:Listadmins causes the site to become unresponsive. It appears that the
page gets stuck on this query:
| 188626 | idxuser | localhost | wikidb | Query | 354 | Sorting result | /*
listadminspage::doQuery */ SELECT ur_rights as type,2 as namespace,user_name as
title, user_name |
And sorts the result for upwards of 10 minutes plus. Since it locks the user
table, access to our pages are blocked waiting for this call to complete.
We have very few admins on our site, maybe 10-15, so it looks like a problematic
SQL call or perhaps the code that migrated our database tables from the 1.3
version (user_rights in the user table) failed to create some necesary index for
the user_rights table.
If you have any questions, let me know.
It does look like if there's going to be a selection based on the value of
ur_rights there should be an index for this column in the user_rights table.
Does this have any effect?
alter table mw_user_rights add index (ur_rights(128));
You might also add "AND ur_rights <> ''" into the WHERE clause in
SpecialListadmins.php to see if it has any affect. On a quick test with 100k
blank users I'm getting fast response either way so it's hard to say.
Note that this is completely different in 1.5, with a decent property table for
I tried adding "AND ur_rights <> ''" to the query first and it didn't help,
although adding the index fixed the problem completely. Was it necessary though
that the conversion code that created this table for 1.4 inserted 1.25 million
rows with mostly empty rights? The user object appears to handle the case where
a user doesn't have a row in user_rights table because it does a left outer join
when loading from the database.
mysql> select ur_rights, count(*) from user_rights group by ur_rights;
| ur_rights | count(*) |
| | 1252452 |
| sysop | 7 |
| sysop,bureaucrat | 3 |
I should note that only adding the index and not changing the SQL statement to
where ur_rights <> '' also has the same problem. For me to fix it, I had to both
add the index and change the SQL statement, which makes sense.
(In reply to comment #3)
> Was it necessary though that the conversion code that created this table for
1.4 inserted 1.25 million rows with mostly empty rights?
No. If the upgrade scripts are bunging a load of redundant rows into the table,
then that's wasteful.
Closing as FIXED since 1.4 is long obsolete and 1.5
doesn't have this problem.
1.4's user_rights table was just the user_rights column
split off from user, and it needed a row for each user due
to the way UPDATEs were done. There is no longer such a
table in 1.5; user_groups is handled efficiently, with no
need for empty rows for users with default permissions.