Optimizing WordPress database performance

March 30th, 2009 – 71 Comments

I’ve been working with a plugin that was mentioned on the wp-hackers list last week, called debug queries, to optimize the performance on some of my sites, including this one. The plugin was pretty easy to use, and I’ve improved a bit on it. I’ve sent the patch to Frank, who authored the original plugin, and he was kind enough to immediately update the plugin.

When you’re logged in as an admin, this plugin now shows the following info in an HTML comment tag in the footer of your pages:

  • the query that was executed
  • the time it took to execute that query
  • the function path that led to this query, with the last function being the most likely to have created the query

I’ve written down some of the things I encountered that you may encounter as well are the following:

Empty options

A lot of WordPress internal settings and plugin settings are stored in the options table. Values in this table take two forms: those which are autoloaded (which is the default) and those which are not. One of the first queries to the database that WordPress does once it starts loading a page, is the query that loads all options with autoload set to “yes”. Hence, any call the an option after that, that happens on multiple pages, is weird. So when I saw this type of query happen a lot:

SELECT option_value FROM wp_options
  WHERE option_name = 'headspace_global' LIMIT 1

I was wondering what was happening. It turned out that that headspace_global option simply didn’t exist, so it can’t be autoloaded. This wasn’t happening with just this option, but with several options. The solution is pretty simple: just add the option as an empty option to your database. This might break some plugins though, and if it does, you should contact the plugin author…

This is actually something plugin authors have to take note of: doing a get_option to check whether a certain option is set or not, is actually more expensive than just setting the option and leaving it empty…

Adding an option to the database is done like this in your MySQL database:

insert into wp_options ( option_id, option_name, autoload)
  values ( '0', 'test', 'yes')

It should be noted that I’ve emailed John Godley, who has built and maintains HeadSpace2, and this’ll be fixed in the next release.

Non cached queries

This happens, of course. If you see a certain query being performed more than once during the pageload, that’s something to optimize. Check what is causing the queries, and then either solve them, or email the responsible plugin or theme author with what you’ve seen (be sure to copy all the queries you see).

Checking whether the blog is installed

The very first query WordPress does is a query to check whether the blog is installed, it’s a pretty useless query if your blog is up and running, so I wanted to get rid of it. There’s no pretty fix to this, so we’ll do it in the not so pretty way:

  1. open up wp-includes/functions.php
  2. find the function is_blog_installed()
  3. add return true; as the first line of the function, and you’re done!

“Heavy” plugins

It turns out, that of the 16 queries my site now still does for a single post page, 7 are for the related posts plugin I’m using, and if you’re wondering: yes that’s making me reconsider the plugin. I’m going to try a couple other plugins and see how good they do in both relevance and (database) performance.

Your experiences with WordPress performance optimization?

Have any experience in optimizing your WordPress performance by tweaking database calls yourself? Any cases I’ve missed? Let me know in the comments!!


71 Responses to Optimizing WordPress database performance

  1. Bird
    By Bird on 28 November, 2009

    I have tried WP super cache but.. I found some problem that I have no idea what the problem is.

    So that I un-install it already

  2. Harsh Agrawal
    By Harsh Agrawal on 18 November, 2009

    I use Dreamhost as my server and they told me they dont support WP problem for plugins like YARPP because it creates lots of php queries and over load the server…

  3. Eastmobiles
    By Eastmobiles on 13 November, 2009

    Thanks for this great post, I came to find that wordpress consumes too much resources, which is a pain in the neck.

  4. Tammy Camp
    By Tammy Camp on 4 October, 2009

    Is there a definitive conclusion for a wordpress database optimizer after all of this? Seems like a lot of mixed opinions on all of them in these comments.

    • Joost de Valk
      By Joost de Valk on 5 October, 2009

      Hey Tammy,

      while I didn’t know it at the time of writing (probably because it didn’t exist yet), W3 Total Cache is probably the best speed improving, database caching etc. plugin there is for WordPress out there at the moment.

  5. MTG
    By MTG on 2 August, 2009

    I had to stop using YARPP as it was causing high resource usage and my site was going down. I do use WP-Super-Cache, WP-Widgets-Cache and DB-Cache for some performance optimization of my WordPress blog. But I am still not fully satisfied.

    • mitcho
      By mitcho on 11 September, 2009

      Sean, MTG, unfortunately this post and many of the comments were written right before my April release of YARPP 3.0 which was a major rewrite of YARPP for performance reasons. YARPP 3.0+ uses a very smart caching system to only run each “relatedness” computation once, so it is much more efficient now. In fact, Efficient Related Posts which was released more recently uses this same technique as well. I invite you to give YARPP a second look and let me know if you still have performance issues with it.

      • Aaron D. Campbell
        By Aaron D. Campbell on 4 October, 2009

        I just thought that I’d post my findings. I still had quite a few problems with the most recent YARPP. When it first scans all the posts, it brought the server to a crawl. Then every time a post was saved it would take more than a minute to save. Switching to Efficient Related posts took that save time down to 2-3 seconds.

  6. Sean
    By Sean on 2 August, 2009

    YARPP is the prettiest but a total performance nightmare. We just had to can it on all of our WP sites as it was causing major problems. Efficient Related Posts is much better, more intelligent design, and not dynamic at post generation time (static meta data embed in posts done at post save time).

  7. steve
    By steve on 29 July, 2009

    Does the results of this plugin consider whether WP Super Cache is installed ?

    For example , I have Super Cache installed. Debug queries tells me I have 50 queries for a page. Have these queries been actually executed provided that Super Cache is on ?

  8. Alex
    By Alex on 21 July, 2009

    What useful info about a useful plugin! Just what I was after! My host has been very patient with my blog burning too many cpu cycles. With this plugin, I’ve reduced the number of db queries to more acceptable levels.

    Excellent stuff!

    Cheers,

    Alex

  9. AYOSIN.COM | stop dreaming start action

    Many thanks, this exactly what I needed, I’m building something other than a blog, but using wordpress as a framework, some of my pages weigh in at 90 queries right now and I need to cut that down as much as I can

  10. Vladimir
    By Vladimir on 18 June, 2009

    Those who are concerned with query optimization may find this plugin useful:

    http://wordpress.org/extend/plugins/sqlmon/

  11. Perfu
    By Perfu on 12 June, 2009

    Great. Thanks for sharing.

  12. FreewareMatter
    By FreewareMatter on 5 June, 2009

    I’m using YARPP plugin and I love the way it works. But with the Debug queries plugin, I realize that it takes many queries, like that:

    (Before)
    Total query time: 0.07755s for 13 queries.
    Total num_query time: 0.470 for 16 num_queries.

    (After)
    Total query time: 1.95269s for 34 queries.
    Total num_query time: 2.714 for 37 num_queries.

    So I decide to turn it off now. But I need another related plugin, do you have some advices?

  13. Marc Pettifer
    By Marc Pettifer on 25 April, 2009

    Many thanks, this exactly what I needed, I’m building something other than a blog, but using wordpress as a framework, some of my pages weigh in at 90 queries right now and I need to cut that down as much as I can.

  14. traveler
    By traveler on 23 April, 2009

    Great post!
    I subscribed to your blog, this is a good source for WP users.

  15. Julian
    By Julian on 23 April, 2009

    I had lot of performance issues when I moved to a new VPS.

    I have compiled PHP in a CentOS with eaccelerator instead of xcache, also I optimized my.cnf for SQL cache in MyISAM tables.

    I have disabled YARPP plugin as well as other unused plugins.

    I compressed style files and tried to make the WordPress theme smaller as possible.

    I am not sure yet about what to do with Global Translator

  16. feir
    By feir on 22 April, 2009

    Great. Thanks for sharing.

  17. IlmondodeiSitiWeb
    By IlmondodeiSitiWeb on 21 April, 2009

    This is a beautiful article.
    I like these suggestions!

  18. Decky Rooroh
    By Decky Rooroh on 20 April, 2009

    This is great article.
    :) Happy blogging !

  19. Uli - Cool Products Design
    By Uli - Cool Products Design on 17 April, 2009

    I’m a little bit scared of modifying the WordPress’ core files, since I’ve experienced two fatal error to my WordPress blog. I edited the file via FTP, and when I saved it back.. zapp.. my blog gone nowhere. it was just a white blank screen. I have no idea about the error. So I re-installed the WordPress.. twice.. with the same problem. Could you propose a safer way to edit the WordPress core files? Thanks.

  20. Ramoonus
    By Ramoonus on 15 April, 2009

    I personally dislike DB Cache since it also cached the dashboard and dashboard related features ….

  21. gigi
    By gigi on 14 April, 2009

    I’m trying debug queries plugin and at each refresh for my homepage i got a different Total query time: first time 2.63149523735046 for 97 queries; second time Total query time: 1.9686913490295 for 97 queries;
    Where is the problem?

  22. Mezanul
    By Mezanul on 14 April, 2009

    I am also using YARPP but I also use Hypercache and WP Widget Cache for making my site load faster. And I do see the difference.
    @Rudy Thanks for telling about DB Cache, I will try that soon.
    @Kaspars Thanks, I will try out WP Tuner and Clean Options.

  23. freekrai
    By freekrai on 8 April, 2009

    Actually, what I found helped with YARPP was turning down title and body check and only using category and tags.

  24. Aaron D. Campbell
    By Aaron D. Campbell on 7 April, 2009

    Rudy…you can tell it NOT to consider the Tags and Categories, which will greatly reduced the number of queries and the server load.

    Unfortunately, matching tags and categories are often a great indicator of related posts!

  25. Rudy
    By Rudy on 7 April, 2009

    After the whole ordeal of switching hosting companies and upgrading from shared hosting account to a VPS hosting account because of excessive CPU usage caused quite possibly by YARPP I learnt a three important things on how to optimized WordPress for speed and light server load.

    1. Use DB Cache to cache the queries made to the database. In some instances it can bring a normal WP blog with 40 or so db queries to less than 20 queries. Also, make sure you add the code to view the number of queries and time in seconds needed to generate the page at the footer of your website. Also, check the number of queries made to the database on the home page and the single page before you turn another plugin and check again after activating the plugin; do this three times to get some averages.

    2. Use Hyper Cache which is very simple to use and was designed specifically for blogs hosted at low resources and cheap hosting companies. Hyper Cache is the best caching plugin I have tried and I have tried them all. Hyper Cache can even cache pages for mobile devices and redirections.

    3. Use YARPP 3.0.b6 if you must have the capability of related posts in your blog. YARPP 3.0.b6 includes caching and has been redesigned to reduced the number of queries and to go easy on the server load. Furthermore, if you use YARPP 3.0.b6 or the previous version, you can tell it NOT to consider the Tags and Categories, which will greatly reduced the number of queries and the server load.

    I hope my experience can help somebody else and perhaps even save some money by not having to switch hosting companies or upgrading to more expensive hosting plan.

    My two centavos. :-)

    • Carlos Guevara
      By Carlos Guevara on 9 August, 2009

      I don’t Recommend using YARPP with any version because it’s very heavy CPU usage is very high when using it , I made a test on both shared hosting also on my local server .
      It eats up your server memory and CPU

  26. Ramoonus
    By Ramoonus on 5 April, 2009

    There`s a plugin called DB Cache
    Although it screws up the dashboard and admin part (updates are visible after the caching time expired)
    It improved my site`s loading time

    I also use Widget Cache, which cached widgets (doh!) which also saves a few queries.

    My biggest problem is the providers fileservers, not their databaseservers.

  27. Widgett Walls
    By Widgett Walls on 5 April, 2009

    When Similar Posts inexplicably stopped working for me, I tried out YARPP and others, but YARPP was crazy CPU expensive. I finally just figured out what the problem with Similar Posts was and went back to it (my settings for how it found the similarities stopped working, tweaking them like mad finally fixed it). I would love to see an alternative, so Aaron, your HV plugin sounds good to me.

    Will try some of the things mentioned in this article as well, starting with is_blog_installed(). Thanks.

  28. Tony K
    By Tony K on 3 April, 2009

    YARPP is a hog. I switched to Rob Marsh, SJ. Similar Posts http://rmarsh.com/plugins/similar-posts/

  29. Aaron D. Campbell
    By Aaron D. Campbell on 2 April, 2009

    First, you should also check out WPDB Profiling.

    Second, we used to use YARPP, but with an investor site that we did which had 6000+ posts and 1800+ tags, it completely choked. Even WP-Related-Posts would cause pages that displayed related posts to take 10+ seconds to load. We ended up having to write our own plugin (which is not released yet, but I hope to eventually). We called it “Related Posts -HV” (for high-volume). Basically, it stores related posts as post meta when a post is saved. It moves the heavy lifting to a one-time thing that affects the post writer, rather than affecting everyone that visits the site. However, you don’t want ONLY older posts to show as related, so you need to re-process a lot of posts when a new one is saved. We created some logic that gives a value of HOW RELATED two posts are. When a post is saved, that value is generated for all posts (comparing the new one to all existing posts). The top X (you can set this value) are saved as post meta. However, every post with a related number > 0 is checked. If it’s related value is 6, but the lowest value of related post currently saved for it is 8, we don’t re-process. However, if it’s 6 and the least-related post that’s stored is 5, we re-scan that post.

    The logic is too much to put in a comment here, but if you’re interested in tweaking with it, let me know.

  30. IgorOsa
    By IgorOsa on 2 April, 2009

    I’m using WP-Tuner and happy about it, but thanks for information on “is_blog_installed()”! It proved usefull!

    • trkyrd
      By trkyrd on 26 November, 2009

      is this how it should be? i have the same problem but i am not sure how to do it.. please the check below..

      function is_blog_installed() {
      global $wpdb;

      // Check cache first. If options table goes away and we have true cached, oh well.
      if ( wp_cache_get( ‘is_blog_installed’ ) )
      return true;

  31. Christian Land
    By Christian Land on 2 April, 2009

    Thats an really interesting PlugIn. I downloaded it and added an “Auto-Explain” Feature to it to see how the queries perform (basically I just checked if the query started with SELECT and added “EXPLAIN” in front of it, called the db with that new query and displayed the results)

    Its really interesting to see what certain PlugIns (or WordPress itself) are doing.

    • Christian Land
      By Christian Land on 2 April, 2009

      Just a little example for things that make you gonna go WOAAAAAH if you add that PlugIn: I’m playing around with the GD-Star Rating PlugIn on a local test-site. Only after using the DQ-PlugIn I realized that GDSR adds a lot of queries… my frontpage goes from 19 to a whooping 51 queries if I add the GDSR plugin (while displaying 10 articles). And a single article page from 24 to unbelievable 42 queries (without any comments – those would increase the query count even more if you add comment-rating, too). Compared to that, YARPP is harmless :-)

  32. Shop Network
    By Shop Network on 1 April, 2009

    I wrote a small little article, Reducing mySQL queries in WordPress. It describes how to reduce the number of mySQL queries when using APC php Accelerator. Take a look.

  33. Anthony
    By Anthony on 31 March, 2009

    Well,

    I’ve been using my own statistics plugin (ZdStatistics) as it includes a number of queries and time to generate pages as well as more general user info. In combination to that, I’ve been watching the code and tried to make as much info as possible into the memory when generating pages. For example on one of my other plugins, I try to put as much info with only one query in memory and then process this stuff.

  34. Jason
    By Jason on 31 March, 2009

    Thanks for the is_blog_installed tip, it all helps! I have recently been using the wp-optimize plug-in mentioned above and it seems to be doing the trick!

  35. Nir
    By Nir on 31 March, 2009

    Thanks, mate, for the great article!

  36. Geek Heaven
    By Geek Heaven on 31 March, 2009

    thanks for the heads up – finally have a nice way of seeing why it takes some pages soo long to load.

  37. Aikido
    By Aikido on 31 March, 2009

    i’m using wp-optimize> plugin, looks quite cool, you can do revisions in your database, Clean marked Spam comments, Clean Unapproved comments, Optimize database tables and you can change your users nickname so easyly if you want.

  38. Navin Poeran
    By Navin Poeran on 31 March, 2009

    Thanks for the cool tips, for sure I’m going to try some out on my own blog :)

  39. John (Human3rror)
    By John (Human3rror) on 31 March, 2009

    let us know when you think of how to do this better than the yarpp…!

  40. Thaya Kareeson
    By Thaya Kareeson on 30 March, 2009

    Let me know what you end up using after YARPP. I use YARPP and I love it too, but I did notice that the only slow queries I have were from that plugin.

  41. Kaspars
    By Kaspars on 30 March, 2009

    Yoost, there are two other plugins that (I think) do the same job a bit better:

    WP Tuner for debugging database queries. The wealth of information this plugin provides is simply invaluable.

    Clean Options for listing and deleting unused entries from options table.

  42. Harsh Agrawal
    By Harsh Agrawal on 30 March, 2009

    Hey thanks for nice post…
    like mentioned by raju even I’m using YARPP, though I love it but there are two things which I don’t like about Yarpp

    One: The signature of YARPP
    two : 7 queries mentioned by you…

    Any Good alternative will be great which also show related posts in feeds.

    • mitcho
      By mitcho on 11 September, 2009

      Harsh, please note that the “signature” you note can be turned off and should be off by default. As I also mentioned elsewhere, since you made this comment YARPP’s performance has been drastically improved. I invite you to give it a second look. :)

  43. Raju
    By Raju on 30 March, 2009

    I am also using YARPP and would love to see a better alternative to it. I have previously used Contextually related Posts plugin and few others, but YARPP seemed more accurate than others.

  44. Kim Woodbridge
    By Kim Woodbridge on 30 March, 2009

    I’ve had trouble with YARPP and had to deactivate on a site with shared hosting. It was the cause of excessive CPU usage.

    • Rudy
      By Rudy on 7 April, 2009

      I went through the same process and I even got VPS account because of excessive CPU usage. I did not know it was YARPP. I wish I had known before going through the ordeal switching servers and upgrading hosting accounts.

      However, I have learnt a great deal in the process and so something good came out of the incident. I am going to leave my THREE tips for optimizing WordPress below which I have not come across in other places just yet.

      • mitcho
        By mitcho on 11 September, 2009

        Joost et al,

        I invite you to try YARPP again, perhaps first on a test site, as YARPP 3.0 (released just after this post and your comments in April of this year) introduced a caching feature which makes it so each relatedness calculation is only done once. YARPP 3.0+ is now much more efficient.

    • eddai
      By eddai on 2 April, 2009

      YARPP was the plugin that caused excessive CPU usage ?
      Gosh..i also had a headache of Excessive CPU usage but YARPP was one of the plugin i did not activate, as it was vital for my site. And later my site was down for 2 days..then i decided to move my site to another hosting..but everything same and my site now uses only %01 CPU. weird..i will keep this in my mind if i’m to face this problem again
      Thanks

  45. Mike Salway
    By Mike Salway on 30 March, 2009

    Thanks for the hints and tips. I also use YARPP and can see it being quite resource intensive, but it also does a great job.
    I’d be interested in hearing of any other “related posts” plugins you find that do an equally good job but use less resources.

    How many plugins are too many? No doubt the more plugins you use, the slower your performance will be.. and of course we can’t guarantee the code quality or optimisation of most of them.

    Cheers

  46. Rarst
    By Rarst on 30 March, 2009

    I hadn’t played much with database. Last time I had terrible performance issues I thought that was DB-related. Turned out one “wise” plugin developer completely disabled Magpie cache for some obscure reason.

    Pulling four feeds on each page load shot performance dead. :)

    Going to try that queries plugin later this week.

  47. Kel
    By Kel on 30 March, 2009

    I haven’t used this plugin for optimization, however I wonder how much, percentage-wise, WP calls the DB when using something like WP Supercache. Would “most users” need to optimize the DB in such a way?

    • Joost de Valk
      By Joost de Valk on 30 March, 2009

      When using WP Super Cache, it would do these calls only when re-generating the page. However, if your site has a lot of pages, a lot of your traffic will be in the long tail, and thus coming to different pages. So if you can optimize it to go from 32 to 16 queries, which I did, this is still a huge improvement for a big percentage of your visitors.


Check out our must read articles about Analytics