Optimizing WordPress database performance
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:
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:
- open up wp-includes/functions.php
- find the function
return true;as the first line of the function, and you’re done!
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!!