ShoutMeLoud – Shouters Who Inspire

Superlinks
≡ Menu
≡ Menu

Reduce WordPress Database Size With These Proven Methods

bluehost
Reduce WordPress Database Size With These Proven Methods

Reduce WordPress Database Size

There are two important components of a typical WordPress installation:

  • WordPress files stored on your server
  • WordPress database

It’s very essential for you to timely cleanup WordPress database and reduce the size. This is a must-have process for every WordPress user to keep the size of their database at minimum and keep their WordPress blog fast. Over the time your WordPress database have many redundant table, unused records and many entries which you can remove without affecting your website. This will help you a lot to reduce load on your server and your WordPress performance will improve.

In this tutorial I will take example of ShoutMeLoud WordPress database which is of size 286.3 MB before cleaning up, and will share exact steps I took to drastically reduce the size of my blog database. You can follow this guide step by step and work on your blog. Only few  of the points might require technical know-how, and if you find yourself questioning there; feel free to ask question or skip that step. So, let’s get on with this simple DIY tutorial.

Tutorial: How to Reduce WordPress Database Size

I’m mentioning steps in the series I followed to reduce the database size, and I suggest you to follow the exact methods.

Step 1: Take WordPress database backup

Taking WordPress Database Backup

This is most essential step which you need to take before you work on your blog database. In my case I have used my hosting company backup feature (WPEngine backup feature) to take complete backup of the database and all files. You can use WP-db manager plugin to take backup of your database file. You can find tutorial over here to learn taking WordPress backup. My recommendation here is WP-db manager plugin as we will be needing to run few SQL query, empty and drop database tables with this one useful plugin.

Step: 2 Disabled and delete unused WordPress plugin:

delete unused WordPress plugin

Now go through the active plugin list of your WordPress blog and first disable those plugins which you are not using anymore. There might be plugins that you use once in a while, So I suggest you can disable them too and install when you need it again. Our goal for today is to completely optimise the database size. Only disabling will not be of much help here, so make you also delete those unused plugins. The reason being is to clean out the database table that are created using those unused plugins.

Need some useful plugins?

Step : 3Delete all spam comments, trash posts and post revisions:

What ever comments you have in spam folder and trash folder, empty them. Similarly delete all posts from trash folder and delete post revisions. You can use WP-optimize plugin to get this done with one click.

Step : 4 Find and clean orphan table:

Now follow this tutorial and install and use Clean options WordPress plugin to find orphan and unused tables in your database. Using this plugin you can clean all such tables. This is quite critical step and use it when you are sure of the table names which you are not using. If you are in confusion, I would recommend skip this step.

Note: Deactivate and uninstall the plugin after using it.

Step: 5Remove unused Meta value from Database (Caution: For tech people only)

This is one step which I use once in a year and suggest you to do it only if you are accustomed with PhpmyAdmin. If you have earlier worked on PHPmyadmin, you can simply follow this easy tutorial and get rid of all unused meta value from database. This won’t be decreasing down your database size significantly but again this is useful if you are highly determined to clean up your WordPress database.

Step : 6 MySql Queries to reduce database size

One of the table which is a major cause of increased database size is wp_commentmeta due to Akismet plugin. Before running below mentioned query, my commentmeta size was 146 MB and after optimisation it reduced to 16.1 MB . Here are two queries which you need to run and you can use WP db manager plugin > Run SQL query option to execute following queries from WordPress dashboard. Run both the SQL command 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 many WordPress installation there might be chances your database table name might be different from what is used in above code. Ex: wp_commentsmeta, so don’t forget to check your DB table name and replace it 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:

This step is again for those WordPress users who have a fair knowledge of WordPress tables. Under 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. Here you can empty those logs which are not useful and drop those tables which was created by any plugins that you are not using anymore.  For example I removed wp_roostsettings which was created by one of the plugin I deleted earlier today.

Step : 8 Optimize database and you will be amazed:

If you have followed all the above steps as mentioned, not it’s time to optimise your database and see how much size you have reduced from your database. You can optimise your database using Optimize option of WP-db manager plugin or you can use WP optimise plugin. I have shared a guide before on Database optimisation here.

As mentioned above my WordPress database size was 286 MB before cleaning up and after all the above mentioned steps my database size is 96.7 MB. Which is a huge reduction of 189 MB. Here are articles which you should read to fine-tune your WordPress blog:

Overall if you have a busy WordPress site you should clean your WordPress database once in every 2 months or at least optimise your WordPress database once every 2 week. Go ahead and start working on your WordPress database size and let me know how much space it was taking before and how much space it’s taking after following the above tutorial.

 

  • Author Bio

  • Latest Post

Article by Harsh Agrawal

Harsh has written 1096 articles.

If you like This post, you can follow ShoutMeLoud on Twitter. Subscribe to ShoutMeLoud feed via RSS or EMAIL to receive instant updates.


{ 18 comments… add one }

  • Ernest

    I just followed your tutorial to clean up my blog database.

    Reply
  • Hasan

    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.

    Reply
  • 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.

    Reply
  • Sam

    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.

    Reply
  • 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

    Reply
  • 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)

    Reply
  • suzyspring

    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.

    Reply
  • 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.

    Reply
  • 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…..

    Reply
  • 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.

    Reply
  • MSI Sakib

    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.

    Reply
  • prasanna

    How can i find out my database size?
    Will you guide me on this issue?
    Thanks

    Reply
  • Nikky

    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.

    Reply
  • Rahul

    Thanks for these tips Harsh, specially 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.

    Reply
    • I. C. Daniel

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

      Reply
      • Rahul

        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.

        Reply
  • Abhishek Raj

    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?

    Reply
  • 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. :)

    Reply

Leave a Comment