If you leave your my-account page with an option for new customers to create accounts without actually buying anything, or if you allow people along the purchase process to create accounts before they complete purchase, you’ll end up with a bloated wp_users table.
Many of my customers have ended up in this situation, with tens of thousands of spam user registrations. This will slow down your database, your actions in the backend of the site, and isn’t tidy.
The process below is technical and requires access to your database. I haven’t used it for these purposes, but there’s a plugin that could potentially help.
User Spam Remover. If you end up using this and find it does this trick, please let me know in the comments section.
1) Backup Your Database
You are going to be making a very large change on your website. This might possibly crash if your server has low resources, or has a long-process kill script activated like they do on WP Engine. There’s multiple ways to do this. If you’re mucking about in the database, you probably already know how to export it via phpMyAdmin. Do that. There’s also numerous plugins like BackupBuddy, WP Migrate DB, etc that can help with this. Or you could do it from the command line.
2) Test this process on a staging site first
Seriously. You don’t want to mess things up on a live WooCommerce site. If you don’t have one, you need to either hire a WP Developer like me to set that up for you, or get better hosting like WP Engine, Flywheel, MediaTemple, or Siteground that have built in systems for doing this.
3) Execute SQL Queries
Thankfully I ran across a post by Mike Jolley with some instructions on how to take care of this.
It requires some comfortability with a tool like phpMyAdmin, which most people have access to through their web hosting cPanels.
Run a select first to ensure you’re not deleting anyone important;
SELECT * from wp_users where wp_users.ID not in ( SELECT meta_value FROM wp_postmeta WHERE meta_key = '_customer_user' ) AND wp_users.ID not in ( select distinct(post_author) from wp_posts )
That selects users with NO ORDERS and NO POSTS
If it looks good, change
SELECT * to
DELETE and run the query again.
After doing that, clear up user meta with:
delete from wp_usermeta where wp_usermeta.user_id not in (select ID from wp_users);
You need to change the wp_ prefixes in these SQL queries to whatever your DB uses.