WordPress How To Bulk Delete Users Using MySQL Query

In rare situations, you may find that you need to manually delete users from your WordPress website using phpMyAdmin.  We recommend that you first try using one of the WordPress plugins specifically designed for bulk deletion.  But there are scenarios where these plugins may not fit your needs.

The most important thing you should do before making any modifications to your WordPress data is to first perform a full database backup.

In the example below, we’re going to build a query that allows us to delete all the users on our website who have the role of Subscriber and who have not contributed a post.

First, we’ll query the database to see how many users will be affected and make note of how many rows were returned:

SELECT ID FROM wp_users WHERE ID IN (  SELECT user_id  FROM wp_usermeta WHERE meta_key = 'wp_capabilities' AND meta_value = 'a:1:{s:10:"subscriber";b:1;}' ) AND ID NOT IN (SELECT DISTINCT post_author FROM wp_posts)

If our query ran quickly and smoothly we can now proceed with deleting these users, which should delete the same number of rows our previous query returned.

DELETE FROM wp_users WHERE ID IN (  SELECT user_id  FROM wp_usermeta WHERE meta_key = 'wp_capabilities' AND meta_value = 'a:1:{s:10:"subscriber";b:1;}' ) AND ID NOT IN (SELECT DISTINCT post_author FROM wp_posts)

Now that we have deleted the users, we also need to delete any orphan data contained in the wp_usermeta table.  Again, we’ll first run a query to audit our command and then run the actual deletion query.

Again, we’ll make note of how many rows are returned in our first query.  The WordPress usermeta table can hold 8 to 20 records per user (more if your site has several user-related plugins).  In this example, we had 2000 users and had 10 usermeta records per user so we should expect approximately 20,000 rows to be returned.

// First we run query to get total number of rows
SELECT wp_usermeta.user_id FROM wp_usermeta WHERE NOT EXISTS ( SELECT * FROM wp_users WHERE wp_usermeta.user_id = wp_users.ID )

// Then we delete those rows
DELETE FROM wp_usermeta WHERE NOT EXISTS ( SELECT * FROM wp_users WHERE wp_usermeta.user_id = wp_users.ID )

We can now return to our Admin dashboard and confirm that everything is in order.