Although there are many different content management systems available to use, WordPress remains one of the most popular today and has won a couple of awards such as the Critic’s Choice Best Open Source PHP CMS and People’s Choice Best Free CMS in 2014. When many websites uses the WordPress platform, this will obviously attract spammers in creating a tool that can automatically spam links to the comment area in a post.
While there are also many antispam plugins to block comment and trackback spam, Akismet remains the top plugin being used to detect spam comments. However these antispam plugins are not perfect as there are times when a spammer finds a way to bypass antispam detection and manage to slip through a few spam comments. Other than that, an unhappy visitor might use these tools to flood a website with thousands of useless comments in hoping to overload the server and causing it to crash.
If one day you wake up to find thousands of spam or pending comments in your WordPress admin area, do not be afraid because there are easy ways to quickly delete the comments. If the spam comments keeps coming in, you should first configure your website to stop accepting any comments.
Login to WordPress admin, go to Settings > Discussion. Tick the “Users must be registered and logged in to comment” checkbox ad click the Save Changes button. Then go to Settings > General and make sure that the “Anyone can register” checkbox is uncheck. This two combination settings will prevent any visitor from being able to post comment on your website. We’ll start to clean up the pending or spam comments once this is done. Here we have 3 different ways to delete all WordPress spam comments.
1. Built-in method
WordPress has a built-in method to easily and quickly remove all spam comments. Go to Comments, click on Spam and you will find a Empty Spam button. Clicking on that button will instantly delete all the comments that are flagged as spam.
There are two possible problems in using the built-in Empty Spam feature. If your website is hosted in an underpowered shared server, the empty spam process might time out and even possibly cause the web server software to crash if you are attempting to remove tens or hundreds of thousands of spam comments. Another possible problem is if the comments aren’t being flagged as spam and appears in Pending, there is no button that can allow you to delete all pending comments.
2. Batch Comment Spam Deletion
If you are facing the above problem where the Empty Spam process is timing out due to large amount of comment spam, you can install a plugin called “Batch Comment Spam Deletion”. This plugin simply modifies the Empty Spam action to delete the spam messages in batch rather than all at once. To install the plugin in your WordPress website, go to Plugins > Add New > search for Batch Comment Spam Deletion at the top right box, and click Install Now button.
There is nothing else to do after installing the Batch Comment Spam Deletion plugin. It automatically modifies the action of the Empty Spam button. If your server is still timing out after installing this plugin, you will have to reduce the number of spam comments to process. From the source code, it shows that this plugin process 100 spam comments at a time and this can be changed by adding a filter in functions or by adding a constant to your wp-config.php. Another quick and easy method is by directly editing the plugin file from Plugins > Installed Plugins > click Edit for Batch Comment Spam Deletion, change the value 100 to a lower number and click Update File button.
3. SQL Command
Using the SQL command is very powerful because you can probably delete all spam or even pending comments in less than a second without time out. However, it is a bit risky because a wrong command can empty a table or even worse, wipe out the whole database. So it is advisable to at least create a full backup of your database using a really easy to use plugin such as “Dropbox Backup & Restore“. After installing the Dropbox Backup & Restore plugin, click on Dropbox Full Backup from the top left bar and click “Create Local Backup” button. If anything goes wrong with the SQL command, simply click the “Restore” button.
You will probably find a lot of examples on other websites telling you to use phpMyAdmin to do it but we think that is a bit overkill because not all web server has phpMyAdmin installed and selecting the wrong database might end up cleaning the comments for other websites hosted at the same account. An easier and safer solution is to install another plugin called SQL Executioner that allows you to execute SQL queries for the WordPress website that it is ran from.
To install, go to Plugins > Add New > type SQL Executioner in the search plugin box located at the top right, and click “Install Now”. After finished installing and activating the plugin, go to Tools > and select SQL Executioner. Click the $comments link to describe the comments table and take note of the table name.
Most of the time the default name of the comments table is wp_comments. You can confirm this by checking the name of the comments table after clicking the $comments link. Now here are a few different SQL commands to delete all spam or pending comments.
To remove all pending comments:
DELETE FROM wp_comments WHERE comment_approved = "0"
To remove all spam comments:
DELETE FROM wp_comments WHERE comment_approved = "spam"
Type the command in the SQL box and click “Execute SQL” button. Click OK button to confirm the query to modify data in your database. The number of rows affected shows the number of comments being removed from database.