WordPress: # users not matching – clean up usermeta

Had a problem on a blog where the number of users didn’t match the number displayed by the admin panel. The solution was to clean up the usermeta table (called `wp_usermeta` in this case).

I tried a lot of different plugins to try to accomplish this, including “WangGuard” (I even registered and generated an API Key). Ultimately, the problem was the need to delete spam users, but doing it manually– and maintaining the integrity of the WordPress database.

“Clean Up Zombie Users (spammers)” also didn’t end up helping me, though I gave it a try. I had already deleted the users (from `wp_users`) manually, but the metadata was still there, in usermeta. I wanted some way to reconcile or reconstruct the database, removing unused usermeta data; I wanted some built-in way to remove any usermeta rows that didn’t correspond to a real, existing user.

Here’s the query I ended up writing and using:

DELETE * FROM `wp_usermeta` WHERE user_id NOT IN (
SELECT `wp_usermeta`.user_id FROM `wp_users` WHERE `wp_users`.ID = user_id)

Here are some references I used:

• Exclude subscriber users from user list
• Remove user id’s stored in arrays that themselves are stored in user meta of another user
• Best practice way to delete user meta data during plugin uninstall?

Here are plugins I tried, although they didn’t meet my needs:

• Clean Up Zombie Users by Imperative Ideas
• WangGuard by WangGuard
• WP-Optimize by Ruhani Rabin

For those with a similar problem, I hope this helps!