Nelio's blog

Latest News and Updates in Nelio's World

fi-spam.jpg

WordPress Database Snippet: Delete all your spam and trashed comments at once

We all get too many spam comments. Most of them automatically detected by our anti-spam pluggin and marked as potentially spam messages for us to review and confirm (either trashing them or accepting them). The problem is that with so many spam around, it’s likely that you, like me, end up having thousands of spam emails that bloat your database.

If that’s your case, removing all spam comments from your dashboard is not an option. Here we show you how to remove all spam comments at once using two simple SQL update sentences on your WP database.

Comments are stored in the wp_comments table where the column comment_approved will tell you whether the comment has been approved, is in the trash or is marked as spam. For instance, this SELECT sentence will quickly show you how many comments of each type you have (this information should coincide with what you see in your Dashboard->comments):

SELECT comment_approved, count(*) FROM wp_comments group by comment_approved

Based on this, the following DELETE sentence will remove all your spam and trashed comments:

DELETE FROM wp_comments where comment_approved='spam' OR comment_approved='trash'

to be on the safe side, let’s make sure that none of these comments had associated metadata and if so we delete it as well doing:

DELETE FROM wp_commentmeta where comment_id NOT IN (select comment_id from wp_comments)

And voilà, now you have a clean dashboard ready to continue enjoying your WP site.

Hope you enjoyed this snippet. Check more useful WP database snippets

WARNING: I don’t think I need to remind you that before any modification to your WordPress site/database you should create a backup, do I? If you make a mistake and transform what should not have been transformed, the backup will save your life!

For more about WordPress, web and social media news check us on twitter , facebook or subscribe to our blog feed or mailing list .

Be sociable, share!

Featured image by Michael Connell

Share this post

Join the conversation