While diagnosing possible causes for a Pligg site running slower than I would like, I came up with some SQL queries to perform some spring cleaning on a Pligg database. The following 3 queries will remove stories and comments flagged as spam, then remove inactive members who haven’t interacted with the site by submitting content or casting votes. I don’t suggest that everyone use these methods to clean up their database. Pligg doesn’t discard spammer data from your database by default so that the spammers can’t just register again on your site. We maintain their user information (email, username, IP address) so that we can use that information to block them.

I have had requests in the past for a way to completely remove these users from the database for the purpose of shrinking the database size. The first query we are going to run will remove all stories flagged as spam. Start by navigating to phpMyAdmin’s SQL tab and then submit the following code:

DELETE FROM pligg_links
WHERE link_status = 'spam'

Next we will remove comments marked as spam from the database. We are running this and the previous query before the third one so that spammers will show up as inactive users in the third step. Once again, run this code from the SQL tab in phpMyAdmin.

DELETE FROM pligg_comments
WHERE comment_status = 'spam'

Finally, we will remove all inactive users from the website. An inactive member is one who does not have any stories, comments, or votes registered in the system. Sometimes we will refer to these members as “zombie” users.

DELETE FROM pligg_users
WHERE pligg_users.user_id NOT
IN (
SELECT link_author
FROM pligg_links
)
AND pligg_users.user_id NOT
IN (
SELECT comment_user_id
FROM pligg_comments
)
AND pligg_users.user_id NOT
IN (
SELECT vote_user_id
FROM pligg_votes
)
AND pligg_users.user_level <>'god'
AND pligg_users.user_level <>'admin'