Some things should be made clear before going in to the topic how to reduce WP_commentmeta Table Database Size in WordPress. First, it is for Self Hosted WordPress CMS users not for wordpress.com users, second is it is quite risky to play with PHPMyAdmin and you must be aware about what you are going to do, a single mistake can reduce your whole blog (that means you need a full backup first) instead of your initial target to reduce WP_commentmeta Table Database Size, third this problem occurs only when we allow WordPress default comment system along with Akismet Plugin and last but not the least – you actually need a good number of spams / day (that depends on your blog’s niche and daily pageviews).
Why we need to reduce WP_commentmeta Table Database Size in WordPress
It might appear an idiotic question if you are not aware that how our WP_commentmeta Table grows bigger with time. We faced (and its a continuous process) this and this post is based on a WordPress forum thread. From 7MB to 72MB – this is the size users reported. It is not a funny thing to get your MySQL database having a huge overhead, you might get infamous MySQL Gone Away error or 500 Internal Server Error.
It happens with any version of WordPress and neither WordPress nor the Akismet plugin itself, my hypothesis is it happens due to those auto spam plugins intended to bomb us to get backlinks. So, probably it has no solution other than we are going to show how to?reduce WP_commentmeta Table size.
Steps to reduce WP_commentmeta Table Database Size in WordPress
Again, you must understand the seriousness of MySQL – one wrong selection and hitting delete can make you crying.
Step 1 to reduce reduce WP_commentmeta Table size
First thing is you need to change the admin.php file in the Akismet plugin with Joseph Scott’s custom admin.php for Akismet. Download it from here. It will itself might not help you to reduce WP_commentmeta Table size, but you will be able to reduce WP_commentmeta Table and optimize it safely using WP-Optimize like plugin. Without this modification, WP-Optimize might not be able to reduce WP_commentmeta Table size.
Turn on maintenance mode. Delete the admin.php from Akismet folder and then paste this modified one written by Joseph Scott. Turn off maintenance mode.
Step 2 to reduce reduce WP_commentmeta Table size
Advertisement---
Now the PHPMyAdmin part. You can easily access PHPMyAdmin from cPanel. If you are using Rackspace Cloud Sites, then go to Hosting > Click your domain’s name > Go to Features Tab > Click the Online Manager link. Do not forget to change the server of choice in the PHPMyAdmin login page.
In any case, if you have forgotten the username-password combination needed for PHPMyAdmin login, simply open your FTP / sFTP and get the database username and password from wp-config.php file at the root of your WordPress installation.
Now click the the database name-link on the left side column. First select all and take a backup. Now click the Browse icon beside WP_commentmeta Table :
You will notice ‘meta_key’ and ‘meta_value’ fields which stores the data of Akismet. Click the SQL Tab :
In the ‘Run SQL query/queries on database’ box, delete the default SQL query (shown for example by default) and copy paste this line and click Go:
1 | Select * FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments) |
You will see the list. Now we need to delete the junks from the table to reduce WP_commentmeta Table size. again, click the SQL Tab and in the ‘Run SQL query/queries on database’ box copy paste this line and click Go:
1 | DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments) |
You will get a confirmation prompt, click OK to proceed. PHPMyAdmin will show the number of deleted rows. Keep in mind, you can not undo any deletion under PHPMyAdmin. Check whether your website is working rightly. You can optimize the Table by selecting it and choosing ‘Optimize table’ option below the tables. This must be done very carefully as selecting an option executes the command. You might have to run it twice or thrice. I will say not to do again and again within few minutes, rather do it the next day if you can not?reduce WP_commentmeta Table significantly. Yes, you must replace the default admin.php file with ?Joseph’s one after every update.
Step 3 to reduce reduce WP_commentmeta Table size
Now install WP-Optimize plugin. Religiously run it after deleting 500-700 spams. For this site, I need to run it daily as number of spams are within this range. I will recommend to go to PHPMyAdmin and do the Step 2 to reduce WP_commentmeta Table manually whenever it exceeds 2 MB size and? WP-Optimize plugin fails to?reduce WP_commentmeta Table from your WordPress console.
