• Home
  • Archive
  • Tools
  • Contact Us

The Customize Windows

Technology Journal

  • Cloud Computing
  • Computer
  • Digital Photography
  • Windows 7
  • Archive
  • Cloud Computing
  • Virtualization
  • Computer and Internet
  • Digital Photography
  • Android
  • Sysadmin
  • Electronics
  • Big Data
  • Virtualization
  • Downloads
  • Web Development
  • Apple
  • Android
Advertisement
You are here: Home » Reduce WP_commentmeta Table Database Size in WordPress

By Abhishek Ghosh December 29, 2011 10:47 pm Updated on December 29, 2011

Reduce WP_commentmeta Table Database Size in WordPress

Advertisement

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.

Advertisement

---

 

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 :

 

Reduce WP_commentmeta Table

 

You will notice ‘meta_key’ and ‘meta_value’ fields which stores the data of Akismet. Click the SQL Tab :

 

run sql query wordpress

 

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:

 

Vim
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:

 

Vim
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 , wp_commentmeta

This Article Has Been Shared 705 Times!

Facebook Twitter Pinterest

Abhishek Ghosh

About Abhishek Ghosh

Abhishek Ghosh is a Businessman, Surgeon, Author and Blogger. You can keep touch with him on Twitter - @AbhishekCTRL.

Here’s what we’ve got for you which might like :

Articles Related to Reduce WP_commentmeta Table Database Size in WordPress

  • Making WordPress to Load Faster on Rackspace Cloud Sites

    Making WordPress to Load Faster on Rackspace Cloud Sites needs WP Super Cache,CDN Sync Tool, HyperDB, .htaccess tricks and tweaks to make buggy plugins working.

  • Make Money with Google AdSense Guide for Dummies : Part 2

    Make Money with Google AdSense article is a series post of our Google AdSense Guide for those who knows nothing about how to earn so.

  • How Fast WordPress Can Load : Our Optimization Results

    How Fast WordPress Can Load ? Have you ever thought what speed default WordPress Installation have the page loading Speed? Its just below one second.

  • MaxCDN versus Rackspace Cloud Files : Head to Head Test and Result

    MaxCDN versus Rackspace Cloud Files – NetDNA versus Akamai. Let us see the test and result in our own eyes to speed up our WordPress setup.

  • Managing and Delivering Podcast Audio and Video From WordPress

    Managing and Delivering Podcast Audio and Video From WordPress can be easily be done in an advanced setup plus you can use CDN or deliver YouTube Video as well.

Additionally, performing a search on this website can help you. Also, we have YouTube Videos.

Take The Conversation Further ...

We'd love to know your thoughts on this article.
Meet the Author over on Twitter to join the conversation right now!

If you want to Advertise on our Article or want a Sponsored Article, you are invited to Contact us.

Contact Us

Comments

  1. AvatarSomeOne says

    January 18, 2012 at 9:26 pm

    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!

  2. AbhishekAbhishek says

    January 19, 2012 at 6:29 am

    You are welcome. I am glad that you liked it.

  3. AvatarReuben says

    February 9, 2012 at 10:38 pm

    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?

  4. AbhishekAbhishek says

    February 10, 2012 at 6:58 am

    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.

  5. AvatarRamnadh says

    April 24, 2012 at 2:09 pm

    Thanks for this Great tutorial… I manged to reduce my database size over 80%.

  6. AvatarJacob Klein says

    June 27, 2012 at 9:50 pm

    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.

  7. AbhishekAbhishek says

    June 27, 2012 at 10:24 pm

    You are welcome Jacob. That is 25 MB SQL storage.

  8. AvatarLuis says

    December 19, 2012 at 10:31 pm

    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!

  9. AbhishekAbhishek says

    December 19, 2012 at 10:41 pm

    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

    wp_commentmeta

    . 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/

  10. AvatarLuis says

    December 19, 2012 at 11:50 pm

    Nervermind, just did it. Thank you very much!

  11. AvatarLuis says

    December 19, 2012 at 11:54 pm

    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!

  12. AbhishekAbhishek says

    December 20, 2012 at 12:59 am

    Oh ! I thought you have already done it !
    The other guide is too much radical.

  13. AbhishekAbhishek says

    December 20, 2012 at 12:59 am

    No problem. Thank you too for the feedback !

  14. AvatarMr Gray says

    October 3, 2013 at 12:48 am

    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?

  15. Abhishek GhoshAbhishek Ghosh says

    October 3, 2013 at 3:13 pm

    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.

Subscribe To Our Free Newsletter

Get new posts by email:

Please Confirm the Subscription When Approval Email Will Arrive in Your Email Inbox as Second Step.

Search this website…

 

Popular Articles

Our Homepage is best place to find popular articles!

Here Are Some Good to Read Articles :

  • Cloud Computing Service Models
  • What is Cloud Computing?
  • Cloud Computing and Social Networks in Mobile Space
  • ARM Processor Architecture
  • What Camera Mode to Choose
  • Indispensable MySQL queries for custom fields in WordPress
  • Windows 7 Speech Recognition Scripting Related Tutorials

Social Networks

  • Pinterest (24.3K Followers)
  • Twitter (5.8k Followers)
  • Facebook (5.7k Followers)
  • LinkedIn (3.7k Followers)
  • YouTube (1.3k Followers)
  • GitHub (Repository)
  • GitHub (Gists)
Looking to publish sponsored article on our website?

Contact us

Recent Posts

  • What is ChatGPT? February 3, 2023
  • Zebronics Pixaplay 16 : Entry Level Movie Projector Review February 2, 2023
  • What is Voice User Interface (VUI) January 31, 2023
  • Proxy Server: Design Pattern in Programming January 30, 2023
  • Cyberpunk Aesthetics: What’s in it Special January 27, 2023

About This Article

Cite this article as: Abhishek Ghosh, "Reduce WP_commentmeta Table Database Size in WordPress," in The Customize Windows, December 29, 2011, February 5, 2023, https://thecustomizewindows.com/2011/12/reduce-wp_commentmeta-table-database-size-in-wordpress/.

Source:The Customize Windows, JiMA.in

PC users can consult Corrine Chorney for Security.

Want to know more about us? Read Notability and Mentions & Our Setup.

Copyright © 2023 - The Customize Windows | dESIGNed by The Customize Windows

Copyright  · Privacy Policy  · Advertising Policy  · Terms of Service  · Refund Policy

We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept”, you consent to the use of ALL the cookies.
Do not sell my personal information.
Cookie SettingsAccept
Manage consent

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
CookieDurationDescription
cookielawinfo-checkbox-analytics11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional11 monthsThe cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy11 monthsThe cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.
Functional
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytics
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.
Others
Other uncategorized cookies are those that are being analyzed and have not been classified into a category as yet.
SAVE & ACCEPT