Delete all subscribers from wp_users and wp_usermeta a few thousand at a time

by

I have a site with 70K spam subscribers, and I don’t need any of them. So I need to delete all subscribers from wp_users and each user’s associated meta from wp_usermeta using a query in adminer and/or phpmyadmin.

But to keep from crashing the server, so how can I delete a few thousand at a time?

This seems to be the basic query I need:

DELETE
FROM  wp_users 
INNER JOIN wp_usermeta ON wp_users.ID = wp_usermeta.user_id
WHERE meta_key = 'wp_capabilities'
AND meta_value  LIKE '%subscriber%
//except how do I select a few thousand at a time?

But how do I select a few thousand at a time?

3 s
3

My favorite way to do something like this is not via database hacking (which I am always concerned will have side effects), but via a command line tool.

For example, giving a functioning WP CLI install, you can simply do this:

wp user delete $(wp user list --role=subscriber --field=ID --number=10) --reassign=1

The reassign parameter is the user ID of the user to reassign content to. We can use “–number” (the limit), even though it is not well documented, because WP_User_Query supports it.

There you have it – a clean, one line command line utility to do operations like this.

I’d recommend in this case you try the subquery first “wp user list..” to see which users it will delete. After running the one-liner, you will see information like this:

...
Success: Removed user 123 from https://example.com/.
Success: Removed user 124 from https://example.com/.
...

Leave a Comment