I’m a performance nut. I’m always looking for ways to optimize things to make them run faster, and since I use queries all the time in WordPress, I figured I might as well study how to optimize WordPress queries too.
WordPress queries are already pretty optimized out of the box, taking advantage of caching mechanisms when available. It’s important that WordPress do this considering the amount and type of data that a typical query returns. For example, let’s take a look at the return data from this query:
This is a super simple query. We are just returning the most recent post from our blog (yes, I could have used
get_post here, but I’m just using this for show). Here is the data that is returned:
Look at all that data for one post (and that’s not to mention the meta and term data that has been referenced as well). Since WordPress takes advantage of caching, we get solid performance from typical queries like this. But when you start using WordPress for advanced queries with lots of posts to thumb through (e.g. 10k posts), your typical speedy query can become a resource hog in a hurry.
Optimizing the Query
Thankfully there are ways in which we can get around some of these performance bottlenecks in the query when dealing with a large number of posts. Let’s examine a few.
One-off queries are queries that are used to show small amounts of data. It could be that you just want to display linked post titles related to the current post, or you may want to display a dropdown of posts to select for a particular option setting.
If you have a persistent caching mechanism enabled for your site, you don’t need to worry about this. WordPress will automatically set caching to false in favor of persistent caching like Memcached.
Caching, although it generally improves performance, does cause extra queries to be run against your database. In specific cases (e.g. no persistent cache and a TON of posts), this can turn your lightening fast query into a slow moving turtle. If we aren’t fortunate enough to be utilizing persistent caching, we can improve our query performance by bypassing caching altogether. Here is how we do it:
'cache_results' to false, we bypass the extra caching queries and speed up the query process. This makes sense if we don’t have persistent caching because without it, we’ve got to make these queries anyways. We may as well optimize the process in the meantime.
'cache_results' flag is a master flag for turning off post caching. It sets both the
'update_post_term_cache' flags to false. If you want more granular control of turning off caching, you can set one flag or the other to false, like so:
Thanks to a conversation with Rarst, I’m going to post some simple unit tests to show the speed difference between the old and the new. Are these unit tests perfect? By no means, but they at least provide some substance to my argument. Let’s take a look at this first match:
I ran the tests on the Code With WP website. For reference, at the time of this writing, there are 11 published posts that are being queried in this test. I ran each test 10 times separately (so only one query at a time) and recorded the results in Excel. No persistent caching mechanism was enabled. Here are the results:
Average Processing Time for Unoptimized Query: 0.014947224
Average Processing Time for Optimized Query (with
'cache_results' set to false): 0.004860735
Performance Difference: 0.010086489
As you can tell, setting
'cache_results' to false improved the speed of the query. Was it a huge difference? Not really – negligible in fact. But when you start to scale WordPress and are dealing with 50k+ posts, the difference is seconds and very noticeable. This is why I always recommend some sort of persistent caching so you don’t have to worry about issues like this.
If You Dont’ Need Pagination, Don’t Do It!
Not every query needs pagination. WordPress uses
SQL_CALC_FOUND_ROWS when dealing with pagination…on every query, whether you need pagination or not. There is a general consensus that while
SQL_CALC_FOUND_ROWS is convenient and only accounts for one query, it is noticeably slower than using the extra query
COUNT, especially when we have already set our
LIMIT clauses (which WordPress does). So to bypass this, there is a nifty parameter called
'no_found_rows' where we can turn this off altogether. Remember, your query must not rely on pagination to make use of the parameter; otherwise, your pagination will break. Here is how we implement it:
Piece of cake. Just set
'no_found_rows' to true and we bypass MySQL counting the results to see if we need pagination or not. This is yet another victory for query performance.
Let’s do a simple unit test on this as well. Again, I’m using the same system as the previous test. It’s running on this website with 11 published posts at the time of writing. Each test is run 10 times separately and recorded in Excel. No persistent caching mechanism was enabled. Let’s take a look at this match:
And here are the results of my testing:
Average Processing Time for Unoptimized Query: 0.015301085
Average Processing Time for Optimized Query (with
'no_found_rows' set to true): 0.014417219
Performance Difference: 0.000883865
The difference here is less noticeable – but again, when scaling WordPress, this can be another area to improve performance.
Just Post IDs, Anyone?
What if you only need a list of post IDs? It doesn’t make sense to get all that data that is normally returned from
get_posts if we just need an array of post IDs.
Fortunately for us, we can utilize yet another parameter called
'fields' and specify that we only want post IDs, like so:
And there you have it. The
$query var will now be filled with an array of post IDs instead of WP_Post objects. Couple this with some of the performance boosters above and for a site with 10k posts, you’ve still got your incredibly fast query speed that you are used to on a site with 10 posts.
Anybody else have any solid WordPress query performance tips? I’d love to hear them (and use them on my own work, too)!
Update: Check out these slides by Alex King on optimizing the query – looks like it was a great talk! http://alexking.org/speaking/core-competency/index.php#/queries-04-wp-query-modifiers