WP: Users with no comments

April 8th, 2013

I’ve notice over the years of having this blog that I’ve collected a bunch of bogus registered users. I’ve put in some measures to reduce the number of them, but that only reduced the frequency of how often they showed up, it didn’t clean out the old ones. I finally sat down and did something about cleaning out the old ones. This isn’t setup to happen automatically, I just ran it manually and probably will continue to do it manually every once in a blue moon.

Easiest way for me to clean up users is to clean out those that haven’t commented and did not register in the past 24 hours. I ran this sql and it took care of the cruft.

 

DELETE FROM wp_usermeta WHERE user_id IN (
    SELECT * FROM (
        select wp_users.ID from wp_users
        left join wp_comments on wp_users.ID = wp_comments.user_id
        where DATEDIFF(curdate(), user_registered) > 1
        group by wp_users.ID
        having count(wp_comments.user_id) = 0
    ) AS p
);
DELETE FROM wp_users WHERE ID IN (
    SELECT * FROM (
        select wp_users.ID from wp_users
        left join wp_comments on wp_users.ID = wp_comments.user_id
        where DATEDIFF(curdate(), user_registered) > 1
        group by wp_users.ID
        having count(wp_comments.user_id) = 0
    ) AS p
);

Leave a Reply

You must be logged in to post a comment.