Optimize WordPress Queries

Share This Post

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

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:

By settings '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.

The 'cache_results' flag is a master flag for turning off post caching. It sets both the 'update_post_meta_cache' and '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 WHERE, ORDER and 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

Have you enjoyed reading Optimize WordPress Queries? Sign up below to get regular content updates and also get my "7 Tips to a Professional WordPress Setup" absolutely free!

About Thomas Griffin

I live and breathe WordPress. I create products around WordPress (Soliloquy and OptinMonster), contribute to WordPress core and do lots of fun development around WordPress in general. You can find me on Twitter, Facebook and Google+.

  • http://www.avinashdsouza.me Avinash D’Souza

    For once, I have ABSOLUTELY no idea of what you’re saying.

    As always, you’ve blown me away with your work ethic and depth of thought on WP in general.

    :-D

    • griffinjt

      Thanks Avi! You are too kind. :-D

  • Jarrod

    Very happy to read this. I love learning these performance tweaks when I find them. Thanks for the knowledge!

    • griffinjt

      No problem – glad you are able to glean from them. :-)

  • Gizburdt

    Very, very useful post. Thank you!

  • http://twitter.com/chriswallace Chris Wallace

    Reminds me of a talk by Alex King: alexking.org/speaking/core-competency/index.php#/queries-04-wp-query-modifiers

    Not sure if you saw his slides or not, but if you did it would be nice if you posted a link to it.

    • griffinjt

      I hadn’t seen it before, but the slides look solid! Alex is an incredible developer so I have no doubt he has lots more of little nuggets of goodness in that talk as well. :-)

      My reason for looking into optimizing the query was for the Featured Content Addon I released for Soliloquy a month or so ago. I need to optimize the query for returning posts to include/exclude from the query, and this post contains some of the results of that. :-)

  • http://www.fatihtoprak.com/ Fatih Toprak

    What a great post. Thank you ,

  • http://twitter.com/joey89924 joey

    useful post.
    MB6S

  • Pingback: How To Optimize WordPress Queries : WPMayor

  • Pingback: How To Optimize WordPress Queries

  • Randy Federighi

    This is such a great article. It is nice to see more about optimizing performance than some topic done to death! I have this bookmarked for sure. Specially thankful for the ‘fields’ trick. Thank you!!!

    • griffinjt

      No problem! :-)

  • tnorthcutt

    “Was it a huge difference? Not really – negligible in fact.”

    Maybe not in absolute terms, but unless I’m mistaken, it was a 22.5% reduction in query time. That’s huge! Even on a site of moderate size, shaving double digit percentages off a few queries would have a pretty significant impact.

    Great post!

    • griffinjt

      That’s true – I didn’t calculate the percentage. It definitely helps on sites with a ton of posts. :-)

  • http://twitter.com/jacobdubail Jacob Dubail

    Thanks for the tips. I wasn’t aware of `cache_results` or `no_found_rows`.

    I believe we need to take care when using `’fields’ => ‘ids’`. If we then want to get any extra data about a post, based on ID, we will incur extra queries that would have been run/cached already within a regular `get_posts` or `new WP_Query`. Is that your understanding, as well?

  • Pingback: Optimizing WordPress Queries : Post Status

  • vrunda

    wow, now a days i am facing same problem with wp query it’s really very helpful to me , but when we apply the filters with wp_query how can we we optimize it, LIKE apply_filters(‘posts_where’,'function_name’) , in function there is join, any one have any suggestion about ??….

  • http://kovshenin.com kovshenin

    It’s worth noting that cache_results is not just about update_post_meta_cache and update_post_term_cache, otherwise there wouldn’t be three flags. Setting cache_results is the exact opposite of optimizing your query.

    If you don’t want WP_Query to fetch post meta and terms data for your queried posts, use the update_*_cache flags to control that.

    If you don’t want WP_Query to cache your post data, you would use the cache_results flag. This means that any action or filter, hooking in after your post retrieval and calling get_post with the ID you’ve already fetched, *will be fetched again* from MySQL, because WP_Query did not put it into object cache, so the WP_Post class will fire a new query, and then add it to object cache either way :) See WP_Post::get_instance for more information.

    A good use case where somebody would do something like that, is when they have to look through thousands, or possibly, millions of posts in one go, like an upgrade routine, in which case there will just not be enough memory to hold all the data in cache for the request. Running clean_post_cache() after each post is also an option, but it’s slower, and a waste of IO with external object cache enabled.

    tl;dr Don’t touch the cache_results flag, unless you’re absolutely sure about what you’re doing. Use the update_post_meta_cache and update_post_term_cache flags to control meta and terms cache with WP_Query.

    • griffinjt

      That’s why I titled the section “one-off” queries. These are queries that don’t have the intention of being used anywhere else. I can see your point about re-fetching the post object again if you have additional hooks and filters that are you going to make use of your query, but if you do that, then it isn’t really a one-off query. ;-)

      Otherwise, I agree with you that you should use it with care. The two individual cache flags are more appropriate the majority of the time.

      • http://kovshenin.com kovshenin

        These are queries that don’t have the intention of being used anywhere else.

        The problem here is that you never know.

        Say, after you’ve done your “one-off” query, you decide to render your post’s title with core’s get_the_title() function, or perhaps render its permalink with get_permalink(). Both functions (any many others) will call get_post() with the ID you’ve already fetched with your “one-off” query, but since your query did not cache your fetched post, get_post() will *actually run a new SQL query* to fetch it again, and then cache its results either way.

        Even if you end up not using any of such functions to output your information (in which case you’d be doing it wrong, since rendering $post->post_title by-passes many filters), think about all the plugins that a user runs, which might hook into various actions and filters after your “one-off” WP_Query runs, and if they’re doing it right, they’re using get_post().

        So while thinking you’ve optimized your query, you’re actually running it twice!

        • griffinjt

          That is assuming you have passed an ID into the parameter and not the object itself. If you pass in a WP_Post object itself, it bypasses the lookup for caching altogether, which would be a faster way to approach grabbing titles, permalinks and other things of that nature with core functions. If there is a filter applied to the query or you are extracting it in raw format, it runs a new query regardless. It appears that the only time the cache is hit is when you have sent in an object that is not a WP_Post object or have passed in an ID. Correct me if I am wrong.

          Regardless, I see your point.

          • http://kovshenin.com kovshenin

            If you pass in a WP_Post object to get_the_title()? Sure. But, seriously, who passes a WP_Post object to get_the_title()?

            When I said “various actions and filters after WP_Query” I didn’t mean *inside WP_Query*, I meant after. This might be the same the_title filter (and a bunch of others) which runs when you call get_the_title(), which explicitly passes the ID (and not an object) to the filter callback, in which case calling get_post() with an ID is just inevitable :)

            You’re not wrong, but it looks like you’re trying too hard for what it’s worth. WordPress’ object caching is one of the most brilliant things I’ve seen. Try to not get in its way too much ;)

  • Pingback: Optimizing Queries | Fifty and Fifty Code Standards

  • husienadel

    thanks for sharing

  • LuyenDao

    What are you thoughts on Transient API functions, would you use it for a query that is commonly run on a site like “Most Popular Posts” that shows up on every page for example. When is it a bad idea to use it? Thanks!

  • Vladimir Prelovac

    HI Thomas, great article. You need a managewp.org button on the site :)

  • http://bueltge.de/ Frank Bültge

    Hi Thomas, interesting analyze and results!

    I use the plugin Debug Objects to identifier the queries and optimize. The plugin list plugin and core queries in different views and so it is possible to optimize on custom development. Maybe also interesting hint.

  • LuyenDao

    Thanks Thomas – i had come across alot of these tidbits before, but you did a great job connecting all the dots and actually backing it up with some data. The “fields” parameter is particularly and fascinating.

    Do you have any comments on “comments” – i noticed in new relic stack traces for simple query grabbing a bunch of posts, it a SELECT COUNT on the comments table, even though that data might or might not be used.

    I guess Fields would be one way to approach it?

  • שגיב אמרי

    Very cool mate, nice idea optimizing like that – i wish there were more tricks but thats a great start ;)