WordPress: Flushing Spam Comments Quickly

While I love a well-configured copy of Spam Karma 2 as an effortless way to keep my blog free of spam, it doesn’t seem to have a way to automatically purge/expire comments after they sit in the spam bin for a while.

Until I can come up with a more automatic solution, here’s the simplest way to purge all binned spam in a few seconds using the phpMyAdmin SQL query window (it’s an icon in the sidebar once you’ve picked a database):

1. Delete all comments marked as spam:

   DELETE FROM wp_comments WHERE comment_approved="spam";

2. Look up all newly orphaned comment metadata entries:

   SELECT m.comment_id FROM wp_commentmeta AS m 
     WHERE (SELECT COUNT(c.comment_ID) FROM wp_comments AS c 
       WHERE m.comment_id = c.comment_ID) = 0;

3. Click “Check All” at the bottom of the results list. (MySQL doesn’t let you subquery a table you’re deleting from)
4. Click “Delete” to the right of “Check All”.

How much faster is this? In the same time it took me to purge 20 comments via the WordPress web interface, I used this process to remove the remaining thousand or so. The actual SQL statements took fractions of a second.

Still… this is a bit outside my usual area of SQL expertise (I normally work mostly with SQLite and simple INNER JOINs), so I welcome cleaner, more automatic ways to do this without resorting to a little PHP or Python script to pass lists of IDs between statements.

WordPress: Flushing Spam Comments Quickly by Stephan Sokolow is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.