I have a wordpress blog, actually i have a lot of them, which uses the bb forum plugin as well as the main site.
This particular forum attracts a lot of spam signups, this can bog down the database once it gets to the point of over 11k or more, and its a security risk (and pain in the butt). I have implemented several plugins to stop sign ups but they still eventually find their way around things.
So today i decided that i really wanted to be rid of all the users that had not commented or posted on the forum. I had previously found plugins that did this for wordpress itself, but they do not work with the bb forums. Often they check if a person has commented on the wordpress, but not if they have posted on the forum.
Anyway i figured it would be a simple mysql query, so hunted about the forum and made the following up
DELETE FROM wp_users
WHERE wp_users.id NOT
IN (
SELECT user_id
FROM wp_comments
)
AND wp_users.id NOT
IN (
SELECT poster_id
FROM bb_posts
)
AND wp_users.id NOT
IN (
SELECT post_author
FROM wp_posts
)
Then of course you need to hunt out and remove all the other meta data, i used something like this
DELETE FROM wp_usermeta
WHERE wp_usermeta.user_id NOT
IN (
SELECT id FROM wp_users
)
It probably pays to check before deleting, and swap the first “DELETE FROM” for a “SELECT * FROM” to view the data.
It also probably pays to delete the user from wp_usermeta by id and other places (will update later)
Hope this helps somebody, let me know of any other places the data needs to be removed from if you know more.
ps. my PC crashed hard whilst writing this, i lost the exact MySQL query i used so redid this from scratch – let me know if i missed any bits or you get any errors.
