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 user_groups.
Hi Brion, 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 | +------------------+----------+
Hi, 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. Thanks.
(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.
