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.

    • Rob says:

      David is correct. ‘no_found_rows’ is set to true in get_posts().

      function get_posts( $args = null ) {
      $defaults = array(
      ‘numberposts’ => 5, ‘offset’ => 0,
      ‘category’ => 0, ‘orderby’ => ‘date’,
      ‘order’ => ‘DESC’, ‘include’ => array(),
      ‘exclude’ => array(), ‘meta_key’ => ”,
      ‘meta_value’ =>”, ‘post_type’ => ‘post’,
      ‘suppress_filters’ => true
      );

      $r = wp_parse_args( $args, $defaults );
      if ( empty( $r['post_status'] ) )
      $r['post_status'] = ( ‘attachment’ == $r['post_type'] ) ? ‘inherit’ : ‘publish’;
      if ( ! empty($r['numberposts']) && empty($r['posts_per_page']) )
      $r['posts_per_page'] = $r['numberposts'];
      if ( ! empty($r['category']) )
      $r['cat'] = $r['category'];
      if ( ! empty($r['include']) ) {
      $incposts = wp_parse_id_list( $r['include'] );
      $r['posts_per_page'] = count($incposts); // only the number of posts included
      $r['post__in'] = $incposts;
      } elseif ( ! empty($r['exclude']) )
      $r['post__not_in'] = wp_parse_id_list( $r['exclude'] );

      $r['ignore_sticky_posts'] = true;
      $r['no_found_rows'] = true;

      $get_posts = new WP_Query;
      return $get_posts->query($r);

      }

Leave a comment

Please post bug reports and feature requests in the Forums.