If you are running a self hosted WordPress blog you must be using the Plugin ‘Akismet’ to get rid of spammers. But sometimes you may notice that, your database size is growing unnaturally. Though the spam comments are deleted by Akismet but still it leaves trace of the spam comments in the form of some meta keys. If you check wp_commentmeta table you may notice some meta keys ‘akismet_result’, ‘akismet_as_submitted’, ‘akismet_history’ etc. These all makes wp_commentmeta table too huge. As the database size is very important for smooth performance of the website, you must clean up wp_commentmeta.
Before the cleaning process, you are advised to take backup of your entire database first. Once you are done follow the steps below,
First, log in to your WordPress blog and check the database size by using plugin ‘WP-Optimize’ and note it down
Log in to your hosting manager
Log in to phpMyAdmin by choosing the right database
Expand the database tab and click on wp_commentmeta
Click on ‘SQL’ tab and Check for junk entries by running the following query,
SELECT * FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments)
If satisfied, delete the junk entries by running the following query,
DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments)
It will ask you for confirmation of deletion. Just say yes.
Now it is time to clear Akismet related meta data in wp_commentmeta table. Again click on SQL tab.
Ckeck for Akismet related meta data in wp_commentmeta table by running the following query,
SELECT * FROM wp_commentmeta WHERE meta_key LIKE "%akismet%"
If satisfied, delete those by running the following query,
DELETE FROM wp_commentmeta WHERE meta_key LIKE "%akismet%"
Again log in to your wordpress blog. Check database size by clicking on WP-Optimize tab. It will show the size as you have seen in step 1. Now, you have to perform the final step. Only check the ‘Optimize Database Tables’ checkbox and click process. After optimization, log out and log in again to see the difference.
Happy Blogging!! 🙂