Skip to main content
WordPress made easy with the drag & drop Total WordPress Theme!Learn More

How to Optimize the WordPress Database

Last updated on:

Every WordPress installation uses a single database for storing posts, pages, menus, links users, comments – essentially all forms of textual and encrypted data. However, the stuff that you upload via the Media Uploader such as images, MP3s, videos and other files aren’t stored in the database. They are stored in the ‘wp_content’ folder. As time goes by, the WordPress database grows in size and generates some garbage data which inflated your WordPress database making it slower.

Why Optimize the WordPress Database?

Space Savings

Maintaining and optimizing the WordPress database is an important task and should not be undermined.

Hang on, take a backup now

Before we proceed any further, please take a backup of your WordPress database. If possible, ensure that the backup database is working. Once you create the backup, simply edit the config.php file (present in the WordPress root directory) to use your backup database and ensure everything runs properly.

A small and optimized database performs much faster compared to a large, cluttered one. Smaller database implies faster response to database queries made by WordPress, which in turn makes your WordPress site perform faster – not just for the visitor, but also for the people writing and updating posts.

What is “Garbage Data”?

The term ‘garbage data’ is used to signify information that needs not be present in the database. Examples of such garbage data would include:

  • Comments in the spam queue
  • Unapproved comments
  • Post revisions
  • Trashed items such as posts and pages

Spam Comments

Out of these, spam comments and post revisions affect the database the most. If your site has a moderate number of visitors and has comments enabled, then rest assured, you’ll be seeing a lot of spam comments. Akismet will automatically filter the spam and put it in the spam queue. However, the comments are still present in the database! And you really have no use for them.

Optimize Database

Pre-optimization statistics

As for post revisions – its a slightly different matter. While writing a post, every time you hit the “Save Draft” button, a post revision is created and saved. Specially for long articles, you write it part by part and save every now and then. Imagine the total size of your article is 50KB and you save it say 10 times. That’s 500KB space consumed in the database, which in actuality should consume 50KB. That’s 450KB of space wasted per long article. Now imagine you have 100 such long articles in your site. That’s 45,000 KB or 45 MB of space wasted in your WordPress database!

How to Optimize the WordPress Database?

Menu Location

WP-Optimize is an excellent plugin that helps you clean and optimize your WordPress database. I’ve tested the plugin with WordPress 3.6.1 and found it to be working perfectly! Install and activate the plugin and navigate to its main present in the left sidebar in the admin dashboard.

Next, check all the options (refer “Pre-optimization statistics” image) and press the “Process” button. This could take a minute depending on the size of your database and the server. It should probably take more time in a shared hosting server. Once the optimization is complete, you should get detailed report of the values that have been removed from the database. Here’s an example:

Total Savings

Thus with WP-Optimize, we’ve saved 16.98 MB – that’s 53.31% of the total database size saved!

Download WP-Optimize

Other cool tricks

You could also disable post revisions feature completely. However, I strongly recommend against it, since it creates more problems than solutions. If you want to disable it, enter this code in your wp-config.php file:

define('WP_POST_REVISIONS', FALSE);

Another efficient way of dealing with bulky databases is to automatically empty the trash, at regular intervals. The following line of code will automatically purge all trashed items every 10 days. Add it to your wp-config.php file:

define('EMPTY_TRASH_DAYS', 10 );

You could change 10 to any number of days  you want. However, that value must be greater than 0.

Thus we’ve learnt how to optimize a WordPress database with nothing but a few clicks of the mouse. What plugin do you use to optimize your WordPress database? Better yet, post some of the statistics here once you run this plugin for the first time!

optimize-wordpress-database
Article by Sourav WPExplorer.com guest author
Subscribe to the Newsletter

Get our latest news, tutorials, guides, tips & deals delivered to your inbox.

18 Comments

  1. manav

    nice blog Sourov…….. keep it up..

  2. makeupsubscribe

    Thank you for good tips. I really don’t like the revision and the code to empty trash is so helpful. Thanks a lot.

  3. marywang

    This is just what I am looking for. I am debating whether I should have revision history saved. Because it can be useful sometimes.

    • Darren Turpin

      You can also limit the number of post revisions via wpconfig.php by adding:

      define(‘WP_POST_REVISIONS’, X);

      Where X is the number of revisions you want to keep (I usually go for 6).

      • Sourav K

        Thanks Darren, great tip! 😀

      • Stephen

        Looks good. Must not work on current articles, though. I looked at one of my posts with a long list of revisions. Nothing changed after I added the above code to the bottom of my wp.config file (used the number “3”).

  4. rate comet

    Great tip, never knew you could do that.

    So many plugins out there, makes it hard to find which ones are useful and which ones are not.

  5. Sneha Malik

    Hi Sourav K …
    Its a great WP plugin …
    Hosting companies provide limited space for databases and filling your database with unwanted copies of article can force you to buy additional database space but this plugin really helps non-techie guys to cleanup database.

  6. sivasakthivel

    Great Blog Sourav..
    Thanks.

  7. Xami

    thanks for sharing nice trick

  8. Telecharger Fraps

    Valuable info. Fortunate me I found your website unintentionally,
    and I am stunned why this accident didn’t happened in advance!
    I bookmarked it.

  9. Rob

    How do you fix the load time?
    is it something to do with the size of the images
    and thats why the page is slow?
    need help

    • AJ Clarke

      Depends on the site there are many issues that can affect page speed, try contacting your webhost they should help you out.

  10. Bilqees Kenchi

    Nice collection. Woothemes has a new ecommerce theme called “Superstore” that is pretty good also.

  11. sewa mobil di solo

    Very good written information. It will be beneficial to everyone who employs it, including myself. Keep up the good work – looking forward to more posts.

  12. sewa mobil di bali

    I am actually glad to read this webpage posts which contains lots of valuable facts, thanks for providing such statistics.

  13. sewa

    Very nice write-up. I certainly love this site. Continue the good work!

  14. Kurt

    Very gold article. I already installed WP Optimize to help improve my site.

Leave a Reply

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

Learn how your comment data is processed by viewing our privacy policy here.