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
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.
Tagged With wordpress _commentmeta , wp site maintenance
I have managed to cleanup about 200MB from my database. I was really wondering what causes so much data in my DB. Thank you very much. Very useful article!
You are welcome. I am glad that you liked it.
This is great info. Thanks for this post.
I had thought that version 2.5.4 (i think) of Akismet had addressed this issue to keep orphaned commentmeta entries from occurring. It won’t remove existing orphans, but will stop new ones from occurring. Is this true? Does that make Joseph’s updated file obsolete?
You are welcome Reuben.
Nope, the problem with new Akismet is still present. For us the default new Akismet is increasing the sql overhead more rapidly. We have again replaced with Joseph’s file. It is somewhat better now than default new Akismet.
Joseph’s file is still useful at least from our experience, but probably Joseph needs to update it again. We need to clean sql every 3 days now to get rid off 6MB+ added database.
Thanks for this Great tutorial… I manged to reduce my database size over 80%.
Just wanted to thank you for this fix. Ran it on my blog and reduced db size from 35MB to 10MB… running WPOptimize religiously now! Thanks for the walk through.
You are welcome Jacob. That is 25 MB SQL storage.
First of all THANK YOU for the tutorial. I am suffering more and more with downtimes and database errors, and looking into my WP_commentmeta I can see why: almost 20 mb of data.
I see this tutorial would be the cure for my blog, but unfortunately I’m not able to proceed with the steps I need to paste the lines into the SQL database.
It gives me this error, on both cases:
” #1146 – Table ‘ohmyrock_net.wp_commentmeta’ doesn’t exist ”
Can somebody please help me?
I can’t understand why this is happenning. I really wish I could wipe out thos extra MB that are loading my site!
Thank you!
You are welcome , I sadly saw your website preview from WP control panel with maintenance banner. Can you take a screen shot of all the tables (avoid sensitive parts like url or mask them). Its bizarre not to find
. Basically I want to see the list of Plugins.
You can follow this method to repair database. We have shown with Rackspace, you can use traditional database, it will also work – http://thecustomizewindows.com/2012/08/repairing-wordpress-database-using-cloud-database-server/
Nervermind, just did it. Thank you very much!
Sorry, I didn’t see your answer. Actually I just had to moddify the above codes with my websites own wp_commentmeta name and wp_comments name. Pretty simple. Once I just changed that, bingo, got 17mb of free database back to my site. It’s on maintenance mode because I’m updating the layout – thankfully nothing to do with databases yet!
Oh ! I thought you have already done it !
The other guide is too much radical.
No problem. Thank you too for the feedback !
Thank you so much for these simple instructions. It helped me reduce my commentmeta size by over 100Mb. Then optimizing brought it down to 1Mb.
Like Luis, I also had to change the database prefix as I’m not using wp_
The question I have now is what can I do with the comments table. It is currently sitting on 49Mb with an overhead of 39.2Mb. Can I just optimize that table also?
Hello Mr. Gray,
Yes, optimize the comments table too.
What we normally do is – we just tick mark to select the table name and select the drop down option.
Abnormally, the number of rows grows disproportionately with time.
I tried another way – changing the Akismet API key after a time after full installation (I import only certain tables – avoiding the wp options table, I wrote here how to). Its time taking and very painful. But, the ‘rate of growth’ declines for few days.