random neuron misfires

This happens quite a bit, and I’m surprised the default WordPress install doesn’t have this fixed, but here’s a fix for cleaning up the duplicate users that will end up in your _users table in WordPress.

In my multi-site installation, that table is called ‘wpmu_users’. If you didn’t change the defaults for your site, it’s probably called ‘wp_users’.

Change this accordingly below for your site and table name.

 
# Create a temporary table and populate it with the unique users
# from the original wpmu_users table
CREATE TABLE wpmu_users_X AS 
       SELECT * FROM wpmu_users WHERE 1 GROUP BY user_login;

# Drop the original table that contains the duplicate users
DROP TABLE wpmu_users; 

# Rename the temporary table to the original table name. This
# effectively 'moves' the temporary table to the original table
# name
RENAME TABLE wpmu_users_X TO wpmu_users; 

# Alter the table attributes to set 'user_login' as a unique key
# so attempts to create a username that already exists will be
# denied
ALTER IGNORE TABLE wpmu_users ADD UNIQUE (user_login); 

Here’s what that looked like at the end of the process:

mysql> ALTER IGNORE TABLE wpmu_users ADD UNIQUE (user_login);
Query OK, 157348 rows affected (0.55 sec)
Records: 157348  Duplicates: 0  Warnings: 0

If you want to identify and kill off inactive users, spammers and such, look into the Inactive User Deleter plugin. I’ve used this with great success on my own blog, and it works great to kill off spam users that manage to sneak past the registration mechanisms, but post nothing.

That’s it, you’re done!