How To Optimize WordPress Database Size in 10 Minutes or Less

Optimize WordPress Database Size
  • Save

Want to optimize WordPress database?

Read on…

There are two important components of a typical WordPress installation:

  • WordPress files stored on your server
  • WordPress database

It’s an essential for you to clean up your WordPress database and reduce its size from time to time. Especially if you are running a Woo-Commerce store on WordPress, this is a regular stuff you should be doing.

Over time, your WordPress database has accumulated many redundant tables, unused records, huge wp_postmeta and many entries which you can remove without affecting your website.

This is a must-do process for every WordPress user to keep the size of their database at a minimum and keep their WordPress blog loading quickly.

This will help reduce the load on your server and your WordPress performance will improve dramatically.

In this tutorial, I will take the example of ShoutMeLoud’s WordPress database which is 286.3 MB and drastically reduces its size. 

You can follow this guide step-by-step and perform these tasks on your own blog.

Note: A few of the points require a little technical know-how, and if you find yourself questioning, feel free to ask in the comments or skip that step entirely. 

Also read: How To Improve WordPress Performance With Advanced Database Cleaner Plugin

Tutorial: How to Optimize WordPress Database Size

Step 1. Take WordPress Database Backup (IMPORTANT)

Taking WordPress Database Backup
  • Save

This is an essential step.

In my case, I have used my hosting company’s backup feature to take a complete backup of the database and all included files. If your hosting, offers backup feature, then use it before you follow the steps below to optimize WordPress database.

You can also use the WP-DB Manager plugin to take a backup of your database file. You can find a tutorial over here. We will also be using WP-DB Manager plugin to run a few SQL queries, empty, and drop database tables (all of which this plugin can do).

Step 2. Disable And Delete Unused WordPress Plugins

delete unused WordPress plugin
  • Save

Go through the active plugin list on your WordPress blog and disable those plugins which you are not using anymore. There might be plugins that you use once in a while, but I suggest you disable them now and re-install them when they’re needed again.

Our goal for today is to completely optimize the database size.

Only disabling unused plugins will not be of much help here, so make sure you also delete those unused plugins.

Speaking of useful plugins:

Step 3. Delete All Spam Comments, Trash Posts, and Post Revisions

Whatever comments you have in your spam and trash folders, delete them.

Similarly, delete all posts from the trash folder and delete all post revisions.

cleanup-wordpress-database
  • Save

Step 4. Find and Clean Orphan Tables

orphan-wordpress-tables
  • Save

Install the WP Advanced database cleaner WordPress plugin & and follow this tutorial.

This plugin will find orphan and unused tables in your database. Using this plugin will help you clean all such tables.

This is a quite critical step and does it only when you are sure of the table names which you are not using.

If you are confused, skip this step.

  • Note: Deactivate the plugin after using it.

Step 5. Remove Unused Meta Values From Database 

(Caution: Technical knowledge needed)

This is something I do once a year and suggests you do it only if you are accustomed to phpMyAdmin.

If you have used phpMyAdmin before, simply follow this easy tutorial and get rid of all unused meta values from your database.

This won’t bring down your database size significantly, but it is useful if you are highly determined to clean up your WordPress database.

Step 6. Useful MySql Queries To Optimize WordPress Database

One of the tables which are a major cause of increased database size is “wp_commentmeta” due to the Akismet plugin.

Before running the below-mentioned query, my wp_commentmeta size was 146 MB.

After optimizing, it reduced to 16.1 MB.

Here are two queries which you need to run. Use the WP-DB Manager plugin > Run SQL query option to execute these queries from the WordPress dashboard. 

  • Run both SQL commands separately.

DELETE FROM wp_commentmeta WHERE comment_id NOT IN (SELECT comment_id FROM wp_comments);
DELETE FROM wp_commentmeta WHERE meta_key LIKE '%akismet%';

Note: For some WordPress installations, there is a chance your database table names might be different from what is used in the above code. It could be something like “wp_commentsmeta”, so don’t forget to check your DB table names and replace them accordingly.

Here are couple more SQL queries which will be useful to reduce database size:

DELETE FROM wp_postmeta WHERE meta_key = "_edit_lock";
DELETE FROM wp_postmeta WHERE meta_key = "_edit_last";

Step 7. Empty and Drop Unused Database Tables

Again, this step is for those WordPress users who have a fair knowledge of WordPress tables.

Under the WP-DB Manager plugin, you will find an option to “Empty/Drop Table” and from there you can see the list of all the tables in your WordPress DB.

Here you can empty those logs which are not useful and drop those tables which were created by any plugins that you are not using anymore.

For example, I removed “wp_roostsettings” which was created by one of the plugins I deleted in Step 2.

Step 8. Optimize Database and Be Amazed

If you have followed all of the above steps as mentioned, it’s now time to optimize your database and see how much size you have freed up.

You can optimize your database using the “Optimize” option of the WP Advanced Database cleaner plugin.

As mentioned above, my WordPress database size was 286 MB before cleaning up, and after all of the above-mentioned steps, my database size dropped to 96.7 MB.

That’s a huge reduction of 189 MB!

Overall, if you have a busy WordPress site, you should clean your WordPress database once every two months, or at least optimize your WordPress database once every two weeks.

Go ahead and start working on reducing your WordPress database size and let me know how much space it was before and how much space it is now.

Speaking of fine-tuning your WordPress blog:

Subscribe on YouTube

  • Save
Authored By
A Blogger, Author and a speaker! Harsh Agrawal is recognized as a leader in digital marketing and FinTech space. Fountainhead of ShoutMeLoud, and a Speaker at ASW, Hero Mindmine, Inorbit, IBM, India blockchain summit. Also, an award-winning blogger.

33 thoughts on “How To Optimize WordPress Database Size in 10 Minutes or Less”

  1. Siraj Mahmood

    Such a effective way because I already had done and apply on my Blog. Actually sometime its not hardly impact because some blogger are using Cache plugins like W3 total Cache therefore we need On zlib compression on server. Optimize database will be work but on the other condition when you don’t use cache plugin. 🙂

  2. Hi Harsh. I’ve just finished cleaning up my redundant post revisions (I in-fact disabled them by editing my wp-config.php file). And now I’m reading this post – a very timely post! . As a newbie WordPress user, i however have one question to ask – Does cleaning up the database reduce any load on the server? I mean, does the database has any correlation with server performance?

  3. Thanks for these tips Harsh, especially for the third tip. I used to mark comments as spam and thought it would delete the comments too, but I was wrong. After deleting all my Spam comments(more than 3000) my DB size has greatly reduced.

    1. For future set your antispam wordpress plugin not to save spam comments.
      I use antispam bee plugin, never had problems with spam.

      1. Thanks, I will do that.
        I never liked the plugins (Antispam bee) that automatically delete spam comments, they also delete comments by actual visitors sometimes.

  4. I wonder using multiple themes also might be causing this DB size issue. May be you might have mentioned that point also here. As i can see that I’ve 6 themes installed on my blog with one being active.

  5. Thanks a lot Harsh brother,
    You did a rocking job by providing these awesome tips to increasing wordpress performance. These will be much effective and increase loading speed of my blog. I will definitely try all these tricks.

  6. sandeep singh

    If anybody having problem to deal with database like non tech or Newbie then just create a backup of database and install plugin called “DataBase Cleaner” and perform quick Optimization to your database.

  7. Abhijit Bangal

    Serious.Serious..Serious… Some serious tips for me. The first thing that came to my mind is I have 300+ spam comments and they are just sitting idle (probably like me hahahaha). I am immediatley going to remove them now.
    Many many thanks for superb tips.
    And I also have a few plugins to delete too…..

  8. Pushpendra Pal

    As suggested by you, I used WP-Optimize plugin to delete post revisions. I am happy after cleaning database, my website database size reduced from 64 MB to just 19 MB.
    Thanks for this wonderful WP database optimization tutorial.

  9. Great article for all blog owners. Thanks for the tips. It will be helpful for my blog. And from my point of view akismet is very useful to reduce negative comments so, we need that plugin for sure. This article will be useful for people who install plugins without knowing what it will do.

  10. Jagdish Kashyap

    Hi Harsh,
    Awesome Guide U Have Updated, I Would Like To Recommend One Plugin “WP Optimize” The Plugin Helps You To Optimize Your Database & Many Other Thing (Like- Unwanted files, logs, Spam Comments etc.)

    Thanks For This Wonderful Article 😉

    – Jagdish Kashyap (@Jkblogjudge)

  11. Swapnadip Chakraborty

    Hi Harsh,

    Thanks for sharing an informative tutorials. Unnecessary files in the WordPress database may slow down your blog. So always clean them to get the bet result from WordPress. Here you have discussed every steps so simply, anyone can easily understand how to do it.

    Thanks & Regards,
    Swapnadip

  12. Hi. We at AndroCID didn’t care to optimize database. Thanks to share fantastic post. This guide helped us to remove lots of post revision database.

  13. Ajay Kumar Meher

    Abhishek..to answer your question, when you reduce db size definitely it reduce load on server. In case of normal web hosting accounts that we purchase for our websites the web server and database server are present on one physical server. So with our db optimization the database server pwrformance gets improved and hence overall server performance improves.

    Prasanna … The easiest way to find db size is from phpmyadmin. You can find size of each table as well as the total size.

  14. Great tips. If one is familiar with PhpMyAdmin, he/she could easily drop / empty the tables from WordPress database which are not in use. However this is only for expert users. I was using redirection plugin and that was storing logs and ultimately increased the database size, I had manually emptied those two tables from db and reduced 20MB size.

  15. Hi, Previously i was facing problem with website load time. I followed the steps mentioned in the tutorial and Bingo !!! It was great to see that the hard work and research done by you have made my life easy. Now the website load within a second. Thanks a lot for publishing such wonderful tutorials or Blogs.

    1. yes Mr. Sumit i did follow the steps it really helped me too and now my site is way more faster then earlier it was at the beginning thank you Mr Harsh for solving problem faced by both of us

  16. I used WP-Optimize plugin to delete post revisions. I am happy after cleaning database, my website database size reduced from 64 MB to just 19 MB. Thanks for this wonderful WP database optimization tutorial.

    1. thanks sanjeev and harsh, previously i was used to wp super cache…i didnt understand its setting…wp optimize awesome…it speedup my site…

  17. Milan Makwana

    Can we test everything by installing wordpress locally ? it would be advantage of quick actions and safety.

    1. Harsh Agrawal

      @Milan
      Yes, you can do that, or you can simply take a backup of the database. If something goes wrong, you can restore the database backup which would put your site into the same state as it was before optimization.

  18. Very useful article. I would add, that if you have the Jetpack Contact Form plug-in enabled to empty feedback. My database lost 10mb just by deleting all that archived feedback that I already get forwarded to my e-mail, anyway.

    Ciao,

    L

Leave a Comment

Your email address will not be published. Required fields are marked *

Scroll to Top
44 Shares
[i]
[i]
[i]
[i]
Share via
Copy link