Ever let your WordPress users database get a bit out of control? If you do not allow user registration but have too many users that have no posts or reason to be on your site here is a quick SQL query to clear those users out of your database. Yes you could probably just delete them from the dashboard, and yes this is a pretty rare use case… but I needed this just now so I’m sharing it!
Make a backup of your database before you read any further. I am not responsible if you delete your users and cannot bring them back. You’ve been warned.
First delete the users, where the
wp_users.ID is not found in the
wp_posts.post_author column. We use distinct here because a single user ID could be the author of 1000 posts, and be returned 1000 times when we only need it once.
delete from wp_users where wp_users.ID not in (select distinct(post_author) from wp_posts);
Think your done? Look in your
Now we delete all the
wp_usermeta entries where the
wp_usermeta.user_id is not found in the
wp_users.ID column. Similair to the last query but just pointing at different tables and removing the distinct since the ID column is already all unique values.
delete from wp_usermeta where wp_usermeta.user_id not in (select ID from wp_users);