Be careful with this - as you may be tossing the wrong users ... so only do this if you understand what you're doing.
Here 3 queries to help you find and remove inactive users on a multisite Wordpress setup, where I have defined "inactive" as
- Registered more than 2 years ago
- Never posted a comment
- Never posted a Post
- Never posted in the (WPForo) forum(s)
For this we will dump the affected user ID's in a temporary table, which we will call "remove_these".
Before doing this, we just want to make sure it is gone, in case we did this before:
DROP TABLE IF EXISTS remove_these;
Next we will create the temporary table based on data we pull from a select statement, because ... well, we're lazy haha.
The select statement looks something like this, where you may have to adjust the table names, these are the table I have used in this example query, where I assume your WordPress tables start with "wp_"
wp_users (users)
wp_comments (comments)
wp_posts (posts)
wp_wpforo_posts (forum posts - if your site does not use WPForo, then just remove the relevant lines)
SELECT
wp_users.ID,
`display_name`,
`user_email`,
IFNULL( `user_registered`, 0),
IFNULL( `CommentCount`, 0),
IFNULL( `PostCount`, 0),
IFNULL( `WPForoCount`, 0)
FROM wp_users
LEFT OUTER JOIN (SELECT wp_comments.user_id, COUNT(wp_comments.comment_ID) as CommentCount FROM wp_comments GROUP BY wp_comments.user_id) as CommentCountTable
ON CommentCountTable.user_id = wp_users.ID
LEFT OUTER JOIN (SELECT wp_posts.post_author,COUNT(wp_posts.ID) as PostCount FROM wp_posts GROUP BY wp_posts.post_author) as PostCountTable
ON PostCountTable.post_author = wp_users.ID
LEFT OUTER JOIN (SELECT wp_wpforo_posts.userid,COUNT(wp_wpforo_posts.postid) as WPForoCount FROM wp_wpforo_posts GROUP BY wp_wpforo_posts.userid) as WPForo1CountTable
ON WPForoCountTable.userid = wp_users.ID
WHERE
( (IFNULL( `CommentCount`, 0) + IFNULL( `PostCount`, 0) + IFNULL( `WPForoCount`, 0) )=0 ) AND
( user_registered<DATE_SUB(NOW(), INTERVAL 2 YEAR) )
ORDER BY `wp_users`.`user_email` ASC;
Run this select query to verify the results.
Next we run this combined query to create and populate our temporary table. In the WHERE statement, you will see that I used "INTERVAL 2 YEARS" (reference), which you can change to a time frame you prefer. For example "INTERVAL 90 DAYS".
CREATE TABLE remove_these
SELECT
wp_users.ID,
`display_name`,
`user_email`,
IFNULL( `user_registered`, 0),
IFNULL( `CommentCount`, 0),
IFNULL( `PostCount`, 0),
IFNULL( `WPForoCount`, 0)
FROM wp_users
LEFT OUTER JOIN (SELECT wp_comments.user_id, COUNT(wp_comments.comment_ID) as CommentCount FROM wp_comments GROUP BY wp_comments.user_id) as CommentCountTable
ON CommentCountTable.user_id = wp_users.ID
LEFT OUTER JOIN (SELECT wp_posts.post_author,COUNT(wp_posts.ID) as PostCount FROM wp_posts GROUP BY wp_posts.post_author) as PostCountTable
ON PostCountTable.post_author = wp_users.ID
LEFT OUTER JOIN (SELECT wp_wpforo_posts.userid,COUNT(wp_wpforo_posts.postid) as WPForoCount FROM wp_wpforo_posts GROUP BY wp_wpforo_posts.userid) as WPForo1CountTable
ON WPForoCountTable.userid = wp_users.ID
WHERE
( (IFNULL( `CommentCount`, 0) + IFNULL( `PostCount`, 0) + IFNULL( `WPForoCount`, 0) )=0 ) AND
( user_registered<DATE_SUB(NOW(), INTERVAL 2 YEAR) )
ORDER BY `wp_users`.`user_email` ASC;
Again: scroll through that remove_these table and make sure the data looks valid.
Finally we remove the users from the wp_users table.
Caution this is PERMANENT.
DELETE FROM wp_users WHERE wp_users.ID in (SELECT remove_these.ID FROM remove_these);
When done, you can optionally keep the temporary table for future reference or toss it like so:
DROP TABLE IF EXISTS remove_these;