A Detailed Guide to WordPress Optimization

WordPress is one of the world’s most popular CMS and blogging platforms and like all content management systems, it is powered by a database. WordPress uses a MySQL database and PHP to interact between the database and the web browser.

Any site powered by a database may run faster or slower depending on the number of queries to the database each time the site is loaded, and how long these queries take.

Large databases can cause your website to run slower, particularly if the code used to query the database is not efficient. This is why your WordPress site may start to seem sluggish after time as more and more entries are added to the database, particularly if you’re installing lots of plugins that all add their own tables and additional database entries.

In order to keep your website running quickly and smoothly, like it was when you first installed WordPress, there are some database housekeeping tasks you can do on a regular basis to keep everything tidy and efficient.

1. Backup Your Current WordPress Database

Before carrying out any of the following steps, it’s important to back up your whole database. This is because accidental deletion or corruption of any important data in your WordPress database could mess up your whole site.

There are two main ways to do this:

A) Manually, using phpMyAdmin

phpMyAdmin provides a visual interface to accessing and interacting with your database. You should find a link to it from the database section of your web-hosting dashboard:

database admin

Once you log into phpMyAdmin, you should see a screen like this:

phpmyadmin

In the sidebar you should see a list of all your databases. Find the WordPress database for the site you want to optimize (you may have to check in your webhosting admin panel if you’re not sure of the name) and click on it. You should then see a screen with all the WordPress database tables listed.

From this screen, you need to click the “Export” tab from the top menu bar:

export database

On the next screen, you need to select “custom” so you can customize the database export. Make sure all the tables are selected for export and choose “Save output to a file” :

database export

Other options to set:

  • Choose “SQL” as the format
  • Check “Add DROP TABLE”
  • Check “IF NOT EXISTS”

Click the “Go” button and your whole database will then be exported as a file, which you can save to a secure backup location.

B) Using a Backup Plugin

There are several plugins that do all this for you if you don’t want to do it manually:

2. Delete and Cleanup Unused Plugins

After you’ve backed up your database, it’s time to start doing some maintenance.

It’s a good idea to start with deleting any plugins you don’t use, and getting rid of the excess bloat they may have added to your WordPress database. Lots of people add plugins to test them out but then don’t delete them properly if they decide not to use them.

Go ahead and delete any non-active plugins from your WordPress dashboard and you should also deactivate and delete any plugins you don’t really use.

Unfortunately, most plugins don’t remove the database tables they added during installation when you delete them.

You can delete these tables manually using phpMyAdmin, however it’s easier to install another plugin that will do the work for you.

Plugins Garbage Collector is a WordPress plugin that will clean up the junk tables left when you delete plugins.

This scans your WordPress database and detects the tables that have been leftover from a deleted plugin (these are the entries in red in the screenshot below):

plugins garbage collector

You can see information about these tables to see how much space they’re taking up in your database and delete them from the same screen.

3. Install WP-Optimize Plugin

WP Optimize plugin

All database optimization tasks can be done manually, but if something is a bit of a hassle and takes time to do, you probably won’t do it as regularly as you should. Database optimization is also not always easy, as you’ll have to manually identify data to be deleted and go through and do this table-by-table, using MySQL commands.

Using this plugin, on the other hand, is so easy, you can optimize your database on a weekly basis to keep your site free from junk and running smoothly.

Go ahead and download and activate the WP-Optimize Plugin.

There are several tasks that this plugin can handle for you that will help to optimize your WordPress database:

WP optimize

Optimize database tables

This runs a MySQL command to compact and defrag database tables and should be carried out regularly.

Clean all post revision

Every time you make a revision to a post in WordPress, a new record is inserted into the database. This makes it easy for you to revert to an earlier version of a post if you’ve made a mistake, but can add a lot of excess data to your database, that you’ll probably never need. This option cleans all post revisions from your database, deleting the excess records.

Clean all auto draft posts and posts in trash

Again, WordPress automatically saves drafts of posts as you’re writing to avoid losing your work in the event of a broken connection or other failure but you don’t need these drafts after publishing a post.

Posts that you’ve deleted are not actually deleted, but sent to trash where they stay for 30 days. The plugin will remove these posts from the database completely so they’re not taking up space anymore.

Remove spam comments and comments in trash

Another useless set of data that will be clogging up your database – remove spam and deleted comments to free up space

Remove unapproved comments

If you’ve not kept on top of your comments and have hundreds sitting in your database, or you suspect most of them are spam anyway, you can delete them easily.

Remove transient options

Temporary objects stored in your cache can also clutter up your database.

Remove pingbacks

Pingbacks are essentially junk comments that are created automatically when somebody links to your site

Remove trackbacks

Trackbacks are similar to pingbacks and can safely be deleted if you don’t want them clogging up your site.

Clean post meta data

Meta data includes information like the author and date when you create a new post. When you delete a post often this data is left as “orphaned” meta data and this can be deleted to free up space in your database.

Clean comment meta data

Similarly, this operation cleans orphaned meta data from comments that have been deleted.

Clean orphaned relationship data

This removes another type of unneeded orphan data from your database.

WP-Optimize also has several tabs running across the top:

Table information can be used to details about the size and number of records in each table in your database so you can identify any particularly bloated tables.

Settings allows you to run a scheduled auto cleanup of your database and keep recent data if you wish (for example you may wish to keep drafts and deleted items from the last 2 weeks).

3. Optimize Your WordPress Setup

Once you’ve cleaned up all this excess data you can stop it from being created again by changing a few of the settings within WordPress:

  1. Turn off trackbacks and pingbacks – if you don’t find these useful, you can turn them off in Settings > Discussion.
  2. Reduce your trash storage limit – you can reduce the space that deleted posts take up by reducing the trash storage time from the default 30 days. To do this, add the following line of code to your wp-config.php file, changing the number to the number of days you want to store trash: define( ‘EMPTY_TRASH_DAYS’, 10 );
  3. Turn of post revisions – if you never use this feature, you can turn it off and save a lot of excess records in the database. Add the following code to wp-config.php: define(‘WP_POST_REVISIONS’, FALSE);

4. Reduce Spam Comments

If your database is getting quickly clogged up with comment spam, you can use a plugin to automatically detect and delete spam comments.

Akismet is one of the most popular and effective comment spam plugins and it’s free, so there’s no reason not to be using it on your WordPress site.

You can also disable comments in older posts, which can help to reduce the amount of spam comments that hit your site. You’ll find this setting in the Discussion Settings page in your WordPress dashboard and you can set a number of days for automatically closing comments after a post is published.

5. Cache Your Database

Database caching caches the results of queries that are made to the database, which is quicker and puts less load on the server than constantly querying the database.

Most caching plugins include an option for database caching – W3 Total Cache and WP Rocket are two of the most popular and fully-functioned caching plugins.

Summing Up

If your site has been growing larger for several years and you’ve never done any database optimization, you should notice quite a performance boost after completing the above steps.

If your site is fairly new, you can make sure it stays running fast by making database optimization part of your normal site maintenance routine. You can use the scheduling function in WP-Optimize to make this even easier.

If you have any other database optimization tips and tricks or useful plugins, please share them in the comments below.

Comments

  1. / Reply

    Very helpful information Terrance. Well written with easy to follow instructions and explanations. I didn’t know much about optimizing my WordPress database. Hopefully this will help speed things up.

  2. / Reply

    Very nice roundup. I also use these tool to optimize my database: phpMyAdmin, Clean Options plugin. I hate to have a heavy database, which can influence to the page loading time.

  3. / Reply

    these are really helpful and useful tips. thanks for the post.

    • paul,
    • May 19, 2010
    / Reply

    very nice!
    lots of useful tips, thanks

    • Sean,
    • May 19, 2010
    / Reply

    Very well done. Must reiterate the third paragraph.

    “Remember that it is crucial for you to backup your database before you embark on any dealings with the database.”

  4. / Reply

    very impressive topic and impressive information .

  5. / Reply

    Thanks for this will share!

  6. / Reply

    So, do you have any numbers on these actions? How does performance improved? How come you choose to cache DB-queries through a plugin rather than directly in the MySQL-server?

    1. / Reply

      Here are some numbers on my development server with cache plugin enabled:

      Before:
      Total query time: 0.03939s for 31 queries.

      After:
      Total query time: 0.00405s for 3 queries.

      As for caching it directly on MySQL server, any good way of doing it.?

      1. / Reply

        Well, Query Caching is quite complex and while i suspect that WordPress developers do a lot of performance work and analyzing different querys with A/B-testing(http://dev.mysql.com/tech-resources/articles/mysql-query-cache.html), it’s not a sole answer to minimizing memory footprint and user download times. Often it’s more prevalent to implement a gateway caching method such as an invisible proxy with the help of Squid and such. The problem with performance in wordpress is that it is very extensible and has to be backwards compatible a whole lot.

        Some good resources here for speeding up the actual user experience, one way to attack the simultaneous download issue and javascript download blocking issue is to download and use the wordpress plugin “head cleaner” which moves javascript to the bottom of the source code and minifies both css and js. Oh, and don’t forget to tag, expiredate and gzip all you content. It makes a great difference on second page load.
        http://code.google.com/speed/articles/
        http://developer.yahoo.com/yslow/

      • Sean,
      • May 19, 2010
      / Reply

      Stefan,

      Plugins make it easy for the less expert among us at the cost of some overhead.

  7. / Reply

    Awesome article, Terrance!

    Just what I needed to get my WordPress install even faster. ;)

  8. / Reply

    Excellent post! Learnt a few new exiting things :)

  9. / Reply

    Some great advice here, I’ll be trying some out this week! Spring clean for the database!

  10. / Reply

    Its an very useful article to read. i had been searching for this for past one month. thanks for Terrance.
    i had facing lot of problems regarding post revisions now i cleared it.

  11. / Reply

    it’s very good advices! i’ll try to do some of that things on my blog. I knew many of that, but here also many new hooks. thanks!

    • Christine,
    • May 20, 2010
    / Reply

    Thanks for this advice, will come in handy.

    • Kimi,
    • May 20, 2010
    / Reply

    Awesome and very useful tips! I am a bit nervous to go to my database lol

  12. / Reply

    Some really good ideas here on optimizing the database. Usually Cache and compression tools do what I need. But you can’t always cache your WordPress build into a static page.

    • Thomas Salvo,
    • June 27, 2010
    / Reply

    The amount of size you will save in deleting revisions pales in comparison to the amount you will save if if you have had your WordPress blog up and running for a while and you delete “_transient_feed” (etc.) entries in the options table. It caches the entire RSS feed for the dashboard entries. It gets very large.

    Forget the other tips. That should be #1.

    1. / Reply

      Fantastic advice Thomas, I’ve been pulling my hair out trying to optimize my wordpress installs following some massive slow down problems. I put pretty much everything into place from the article (which is all GREAT advice everyone should heed – I only wish I found it before learning all about it on my own, piece by piece) and things improved but the sites were noticably slow. I pulled out the _transient_feed lines in the wp_options table and the site snapped right back to traditional performance.

      How often do you recommend clearing out those lines? Should I also pull out the _transient_timeout_feed lines?

      Thanks,
      Drew

    • vijay,
    • October 25, 2010
    / Reply

    Nice info.

    • Hayden,
    • December 10, 2010
    / Reply

    Can/should define(ENABLE_CACHE, true); be removed if I install DB cache reloaded?

  13. / Reply

    Hi,

    you don’t really know HOW you helped me with this very nice POST.

    I optimized 3 websites with this help.

    Thank you very much and wish all best !!

    Stefan

  14. / Reply

    Nice one Terrance,
    I am using few of them, and found 2 new one and helpful (Clean Options and Debug Queries) but m not getting the results as defined in Debug Queries. only seconds and queries are desplayed. could you help it?

    • Ruben.cc,
    • July 28, 2011
    / Reply

    Please be advised that adding define(‘WP_ALLOW_REPAIR’, TRUE); to your wp-config IS A BAD IDEA!

    It allows ANYONE (even bots) to run the script /wp-admin/maint/repair.php on your site!

    People (or bots) with bad intentions can excessively execute this script, causing a unwanted load on your server.

    /wp-admin/maint/repair.php is meant as a resort to repair your database if your database it is messed up in a way that you can not logon to your site anymore, that’s why you can enable this option in wp-config.php to fix things. It is NOT meant to be a permanently ‘true’ setting. Please check the wordpress codex site if you want to know the in’s and out’s of options in wp-config.

  15. / Reply

    Thanks for the tips. Optimizing the database is so technical, but this is easy to understand.

  16. / Reply

    Excellent tips. I am installing the Debug Queries plugin now!

    • Oumz,
    • October 26, 2011
    / Reply

    Excellent Tips! I would install Debug Queries Plugin. However, since Database optimization and overhead removal is essential I recommend using WP Sanitize plugin http://wordpress.org/extend/plugins/wp-sanitize

    • Chad,
    • May 27, 2012
    / Reply

    3 days! That’s how long it took to find an 8-second hang up on my site before every page load. 2 of those days were spent diagnosing the server, pouring over FastCGI documentation (I was convinced there was a faulty directive). On day 3 I echoed the number of queries into the footer of my dashboard. The number ranged from 250 to 300. I wasn’t sure what was normal for WordPress. I tried to cache the queries with no luck. I poured over tons of blogs on optimizing databases but this is the only one that had the answer (specifically, the advice to install the Debug Queries plugin, which actually redirects you to a better version of itself–Debug Objects). I checked the queries box and there they were, over 250 “ALTER TABLE” queries from a plugin named Easy Contact Forms. After deleting it, my page loads went from 8-15 seconds to an average of less than one, with the number of queries hovering around 5. I can’t thank you enough for steering me in the direction of an awesome plugin.

  17. / Reply

    Today I learnt something new. Excellent article buddy :) Am using VPS and running out of disk space they are asking to upgrade. I must try these methods to reduce disk space.

Leave a Reply

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

Deals

Iconfinder Coupon Code and Review

Iconfinder offers over 1.5 million beautiful icons for creative professionals to use in websites, apps, and printed publications. Whatever your project, you’re sure to find an icon or icon…

WP Engine Coupon

Considered by many to be the best managed hosting for WordPress out there, WP Engine offers superior technology and customer support in order to keep your WordPress sites secure…

InMotion Hosting Coupon Code

InMotion Hosting has been a top rated CNET hosting company for over 14 years so you know you’ll be getting good service and won’t be risking your hosting company…

SiteGround Coupon: 60% OFF

SiteGround offers a number of hosting solutions and services for including shared hosting, cloud hosting, dedicated servers, reseller hosting, enterprise hosting, and WordPress and Joomla specific hosting.