Speed up WordPress WP_Query 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:
query_posts('no_found_rows=true&cat=1&numberposts=1');
Hope this will help WordPress developers around the world lower their carbon footprint.
UPDATE 25 Oct 2016: I checked on WordPress 4.6.1 and now get_posts sets no_found_rows=true automatically. query_posts and WP_Query do not, so you should still set no_found_rows=true when using them and don’t need pagination.
Does this even work when you do need pagination?
Thanks for the help!
No, pagination won’t work when you pass “no_found_rows=1”
[…] 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 […]
Flavio
this is simple stunning!
thanks for sharing!
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.
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
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!
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.
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);
}
very useful.
thanks for sharing