Mass Delete Spam Comments in WordPress

So, the spam is getting out of control on your WordPress blog? Even if you have activated the Akismet plugin – and you should – SPAM may keep filing up the comments.

You can just let it ride and those comments identified by Akismet as spam will silently disappear in a month’s time. In the meantime your database will be getting bigger and bloated with thousands of lines of useless information. Will this bog down your blog? Perhaps it will.

I found another reason to manually delete all the spam comments on one of my WP blogs. From the WP Dashboard I saw 1000+ spam comments and went to edit the comments. When I clicked on ‘spam’, the anti-virus software on this computer, AVG, detected a threat on the spam comments page and blocked my access to it. Even after shutting down the browser and returning to the edit spam comments page another day, the AVG software wouldn’t let me in. How could I delete the spam if I can’t get to the page to do it? Even if I could get to that page it would take a long time to delete so many comments. There must be a better solution to getting rid of spam comments in WordPress.

So, how do you mass delete spam comments in WordPress? Check out this excellent video that provides a solution using phpmyadmin and your WordPress blog database.

Steps to follow:

  1. sign in to CPanel
  2. open up phpmyadmin and select the WP database
  3. click on “wp_comments” table and browse to see the comments
  4. backup database by clicking on database name in left column, click on export tab, select all tables under Export, select SQL, check “save as file”, click Go to download database backup to your computer
  5. select comments table, by clicking on the table name wp_comments, then click on the SQL tab
  6. use an sql query to mass delete spam comments in WordPress by typing the following in the Run SQL Query box:

    DELETE FROM wp_comments WHERE comment_approved = 0

    Click Go, click OK.

  7. Comments deleted!

If you are using a spam catcher, like Akismet, the value for comment_approved may be set to “spam”, so you’ll need to alter the query as follows:

DELETE FROM wp_comments WHERE comment_approved = ‘spam’

Don’t forget to use the single quote marks to enclose the word spam.

Verify that the number of records in the comments table has been reduced to the number of approved comments. Hooray!

Optimize the WordPress database by going to the Structure tab, or clicking on the database name in the left column, and at the bottom of the table listing click on “Check tables having overhead”. Choose “optimize table” from the spin box on the right and all the extra space that those nasty spam comments took up will be released and the database optimized.

Go back to database view and verify that the overhead space has been removed. The size column now represents the space that is actually used by the database.

Refresh your WP blog and the spam comments will be gone. Isn’t is great to see ZERO spam comments?!

Alternatively, you can catch spam before it lands in your WP blog by modifying the function.php file in your theme using these excellent directions from Tejaswini.

Also, try Soumen Halder’s list of ways to reduce comment spam on WP blogs.

Reblog this post [with Zemanta]

17 thoughts on “Mass Delete Spam Comments in WordPress”

  1. Thanks for very informative information, but the spam is neverdie because is will come in other form instead.

    Delete manually is like mouse and cat game.

  2. Thanks for instructions! They really helped me a lot! (I was a bit scared of that “DB management stuff”, but now I’m not! :)) Thanks!

  3. That’s great, Natalie! You’re welcome.

    It can be a little frightening at first to dig into the DB, but once you take that first step it does get easier.

    Good luck with your sites!

  4. Hey Ryan,

    The only way would be to manually delete them. Once spam comments are marked approved they are no longer considered spam or recognized as such.
    Good luck!

  5. Hi there,

    This is the clearest post I’ve read about this problem, so thanks. Do you know if there is a way of deleting spam comments that have been marked ‘approved’ without deleting my legitimate comments, which are also marked ‘approved’?

    Ryan

  6. What about comment_meta table? This table is swelling even faster than the comment table. It seems each comment is also generating three or four lines in the meta table. Any MySQL command to delete both spam meta and comments?

  7. Hey Scott,
    Try this for deleting the wp_commentmeta entries that don’t have a corresponding entry in the wp_comments table. In other words, we’re deleting the spam entries in the wp_commentmeta table:

    DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments)

    Any comment_id that is not in the wp_comments table will have been marked as spam or manually deleted.

  8. Alright but how about the pending approved comments? It takes years to filter the genuine comments out of the list?
    But no doubt, this sql query is a good way to get the mud out 🙂

  9. Hey Marshall,

    I’m not sure what you mean by it taking years to filter the real comments out of the list. My pending comments are very manageable, thanks to Akismet. It seems to knock out the spam straight away. Only a couple of times have I been mailed with a new comment notice but by the time I got to the site Akismet found it to be spam, deleted it, and I had to take no other action.

    I like your way of saying, “get the mud out”. Hope the sql queries do just that!

  10. Thanks for this tutorial about mass delete spam comments. I dont understand why no one has done a plugin for this by now. There is delete pending plugin but no mass delete comments…

    Once again thanks for the tutorial about this. Thanks.

    Ren

  12. This worked for me clearing spam comments and akismet comment meta data out in one query:

    DELETE a,b
    FROM wp_comments a
    LEFT JOIN wp_commentmeta b ON (a.comment_ID = b.comment_id)
    WHERE a.comment_approved = 'spam'

    You could also amend the last query to:

    WHERE a.comment_approved = '0'

    For clearing out unapproved comments.

    This is on a WP 3.4 + site and I always backup the database before performing delete queries.

  14. Pingback: ¿Recuerdas el blog? Volvio en forma de fichas | SCESI BLOG
  15. Pingback: My Essential WordPress Plug-ins and Website Tasks

Leave a Reply

Your email address will not be published. Required fields are marked *