Reduce Huge Database Size Caused by Akismet

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,

[dt_list style=”2″ bullet_position=”top” dividers=”false”] [dt_list_item image=””]First, log in to your WordPress blog and check the database size by using plugin ‘WP-Optimize’ and note it down[/dt_list_item] [dt_list_item image=””]Log in to your hosting manager[/dt_list_item] [dt_list_item image=””]Log in to phpMyAdmin by choosing the right database[/dt_list_item] [dt_list_item image=””]Expand the database tab and click on wp_commentmeta[/dt_list_item] [dt_list_item image=””]Click on ‘SQL’ tab and Check for junk entries by running the following query,

[dt_code]SELECT * FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments)[/dt_code][/dt_list_item] [dt_list_item image=””]If satisfied, delete the junk entries by running the following query,

[dt_code]DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments)[/dt_code]

It will ask you for confirmation of deletion. Just say yes.[/dt_list_item] [dt_list_item image=””]Now it is time to clear Akismet related meta data in wp_commentmeta table. Again click on SQL tab.[/dt_list_item] [dt_list_item image=””]Ckeck for Akismet related meta data in wp_commentmeta table by running the following query,

[dt_code]SELECT * FROM wp_commentmeta WHERE meta_key LIKE “%akismet%”[/dt_code][/dt_list_item] [dt_list_item image=””]If satisfied, delete those by running the following query,

[dt_code]DELETE FROM wp_commentmeta WHERE meta_key LIKE “%akismet%”[/dt_code][/dt_list_item] [dt_list_item image=””]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.[/dt_list_item][/dt_list]

Happy Blogging!! 🙂

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.