Speed up WordPress get_posts and query_posts functions

While working on a WordPress theme, I noticed a low MySQL query cache hit rate. There were a lot of SQL_CALC_FOUND_ROWS queries that were not cached and were slowing down the database.

It appears WordPress uses SQL_CALC_FOUND_ROWS in most queries in order to implement pagination. Even when you don’t need pagination at all. SQL_CALC_FOUND_ROWS tells MySQL to do additional work to count the total matching rows and this can make a lot of difference if you have a big archive.

By inspecting WordPress 3.x source code I found an undocumented parameter that can be used with get_posts and query_posts functions. The parameter is named no_found_rows and it accepts boolean values. It tells WordPress not to count the total rows. Here’s a usage example:

get_posts('no_found_rows=true&cat=1&numberposts=1');

Hope this will help WordPress developers around the world lower their carbon footprint.

  1. Bradley says:

    Does this even work when you do need pagination?
    Thanks for the help!

  2. [...] out how many posts there are, which was a suggestion I got from Flavio Tordini’s post on speeding up WordPress get_posts and query_posts functions. Whether it works or not, I’m not sure– since I didn’t see a change in response [...]

  3. Monika says:

    Flavio
    this is simple stunning!

    thanks for sharing!

  4. Tamas says:

    Thanks, I found this page when I was looking for the documentation of this parameter, as I’ve seen it in the source too…

    Didn’t know that this was undocumented, but it really helps if you have a large database with thousands of posts.

  5. Emil says:

    How does the query cache relate to SQL_CALC_FOUND_ROWS? According to MySQL documentation query cache is working for SELECT SQL_CALC_FOUND_ROWS queries: http://dev.mysql.com/doc/refman/5.1/en/query-cache-operation.html

    The query cache works for SELECT SQL_CALC_FOUND_ROWS … queries and stores a value that is returned by a following SELECT FOUND_ROWS() query. FOUND_ROWS() returns the correct value even if the preceding query was fetched from the cache because the number of found rows is also stored in the cache. The SELECT FOUND_ROWS() query itself cannot be cached.

    SELECT FOUND_ROWS() queries are reading single value from the cache(i.e. no computation is involved in them) so they’re lighting fast.

    Thanks!

  6. David says:

    no_found_rows is not needed on get_posts, as get_posts returns an object that does not paginate. no_found_rows is already part of the function.

Leave a comment

Blog comments are not for bug reports and feature requests. Please post them in the Forums.

Get in touch!

For technical support, bug reports and feature requests you can post in the Forums.

You can contact me via email on flavio.tordini@gmail.com or ask me a quick question on Twitter.

Please, don't use Twitter for licensing issues and things that will ultimately require your email address.