A Detailed Look Into WordPress Database Optimization

A Detailed Look Into WordPress Database Optimization

It is a known fact that WordPress is powered by PHP and depends on MySQL database; in fact, all the content, including the website settings, are stored in it. When your WordPress site is visited, the web server will query the database to retrieve all the necessary information to display; a typical operation for most of the dynamic site. However, over time, the speed required for querying the database will increase due to the number of operations such as insert, updates, and the delete of data entries.

To restore to the original fast and smooth communication between your web server and the database, housekeeping for your WordPress database needs to be done. This is necessary to keep it running at its peak.

WordPress Database Optimization

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

WordPress Database Statistics

It’s always good to find out what the current status of your database before you begin optimizing it. This will help you to keep check on the before and after status once the optimization of your database is completed.

Output the no. of Database Queries

One simple way to discover the number of WordPress query outputs and the time it takes to complete is to use the following WordPress function:

<?php echo get_num_queries(); ?> queries in <?php timer_stop(1,3); ?> seconds

Debug Queries

It may not be sufficient to only know the number of WordPress query outputs in order for you to troubleshoot and optimize your database. You will also needs the Debug Queries plugin to help you find out what are the queries being executed when the page renders.

Debug Queries
Debug Queries

Once you know your WordPress database statistics, you can now start to optimize your database. Analyze the queries to determine which plugin is causing extra queries that slow down your site. If you discover that a particular plugin is resulting in too many queries, you might want to reconsider a substitute plugin that does the same task.

Optimize Database Tables

As a start, try optimizing the overheads on your database as reported in phpMyAdmin. Overheads are usually caused by too many insert, update and delete activities on your database tables. When this occurs, you need to run Optimize Table manually via phpMyAdmin or by using WordPress Plugin. This will help reclaim the disk space and defrag your database tables.

WordPress Plugin

If you are not familiar with or find it troublesome to login to phpMyAdmin to optimize the overheads of your database, you can chose to do so with WordPress Plugin. These plugins will allow you to select the tables you wish to optimize and will also show you the total size of your database and how much of it can be reduced after you have completed the optimization.

Optimize DB
Optimize DB

WP Optimize
WP Optimize

phpMyAdmin

Using phpMyAdmin is the easiest way to optimize the overheads of your database without adding plugin load to your WordPress site. Simply login to your phpMyAdmin and select the option 1: Check tables having overhead . Then at the side in the drop down box, select 2: Optimize Table.

phpMyAdmin

Edit wp-config.php File

There are a few configurations you can declare in wp-config.php so as to enable easy WordPress database maintenance or optimization.

Empty your Trash

WordPress 2.9 has added a new functionality for the posts, pages, and the comments. Instead of permanently deleting these, you can now trash it and then later empty the trash, in case you might want to restore it. By default WordPress will empty the trash every 30 days, but you can change the time limit to 10 days simply by entering the following code in your wp-config.php.

define( ‘EMPTY_TRASH_DAYS’, 10 );

Repair your Database

WordPress 2.9 also introduces another feature that enables your database to be automatically repaired periodically. To setup the automatic database repair option, simply add the following line in wp-config.php.

define('WP_ALLOW_REPAIR', TRUE);

If you want to manually repair and optimize the database, you can visit: http://www.yourwpsite.com/wp-admin/maint/repair.php to do so.

Turn off Post Revision

With WordPress 2.6, there is a new feature that enables you to have post revisions, just in case you need to restore to the previous version. However, one issue for this feature is the number of extra records being inserted into the WordPress database. Over time, your database will be cluttered with plenty of redundant post revision entries. If you do not need this feature, you can easily turn it off by adding the following line to the wp-config.php file.

define('WP_POST_REVISIONS', FALSE);

SQL Queries for WordPress

In our previous article, 13 Useful WordPress SQL Queries You Wish You Knew Earlier we covered several SQL queries that can help you trim down your WordPress database.

SQL Queries

For easy reference, I have listed the queries below.

Delete Post Revision

DELETE a,b,c FROM wp_posts a
LEFT JOIN wp_term_relationships b ON (a.ID = b.object_id)
LEFT JOIN wp_postmeta c ON (a.ID = c.post_id)
WHERE a.post_type = 'revision'

Delete all Pingback / Trackback

DELETE FROM  wp_comments WHERE  comment_type = 'pingback';

Note: To delete trackback, change the pingback to trackback in the above SQL statement.

Delete all Spam Comments

DELETE FROM  wp_comments WHERE  comment_approved = 'spam';
  • 0 = Comment Awaiting Moderation
  • 1 = Approved Comment
  • spam = Comment marked as Spam

Delete Post Meta

DELETE FROM  wp_postmeta WHERE  meta_key = 'your-meta-key';

If the following standard WordPress meta keys are taking up too many entries in your wp_postmeta table, you can try to do a cleanup from time to time.

  • _edit_lock, _edit_last – Prevent another user from editing the same post at the same time.
  • _wp_old_slug – Remembers that old slug even after you changed it.

Above is an overview of the SQL queries you can use when you are optimizing your WordPress Database. For more details, you can read more on our previous article – 13 Useful WordPress SQL Queries You Wish You Knew Earlier.

Clear Away Unused Table in Database

Very often, you test out a plugin that might seem useful only to realize that it’s something you do not need at a later stage. In the process, some of these plugins create new table(s) in your database to store extra information. However, even after you’ve deactivated and deleted the plugins from your folder, those extra tables of data are still left in your database. Delete the extra tables if they are not in use.

However, be careful not the delete the essential tables. Below is the list of standard WordPress tables that you should NOT delete.

WordPress Tables

Clear Away Unused Entries in wp_options Table

Most plugins need to store bits of data in the database, especially those that have an option page and allow users to make changes; it is unavoidable. All these settings data can be easily stored in the wp_options table. So what happens after you have deactivated and deleted away the plugins? All those settings are left behind. Over time, if you have been trying out quite a number of plugins, the number of entries and the size of your wp_options table will shock you. To minimize this issue, remove the entries that are no longer needed.

It is a tedious task to go through all the entries in the wp_options table to identify the additional entries that is a result of the deleted plugin. Also, if you delete the wrong entry, your site might end up falling apart.

Here we have a plugin that can assist you to identify redundant entries in your wp_options table.

WordPress Clean Options Plugin
WordPress Clean Options is a plugin that can help identify redundant entries in your wp_options table. The plugin will identify options that are no longer being used by your WordPress site by analyzing whether there is any link to the options. Once the plugin has scanned through the wp_options table, you will be presented with the list of all the orphaned options.

Clean Option

However, don’t be complacent. You will still have to look at it individually and carefully. Tick the ones that you really don’t need any more to remove it.

Cache Your Database

After the database housekeeping for your WordPress site, the final step is to cache your database if you have not done so. This helps to reduce the amount of SQL queries from being called to the server by caching the queries to the database which will speed up the performance of the WordPress site.

DB Cache Reloaded
Cache Reloaded

Optimize Now!

We hope that the WordPress database optimization tips we have shared with you in this article will be able to help you optimize your WordPress database to reduce the size and speed up your database operation. This maintenance process should be an ongoing one. Schedule your maintenance at least once a month to ensure that your WordPress database is always running in peak condition.

If there are more WordPress database optimization tips that I have not covered, do share with us your tips in the comment box 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