WordPress: SQL_CALC_FOUND_ROWS, why it’s slow and what to do about it

UPDATE: 25 Feb 2017 This has actually become the most popular post on my site over the last few years and recently someone was kind enough to point out that the original code, well mostly working, ignores any params that might be passed to WP_Query. So I went over it again and came up with a much better way of doing it. Check out the new code below.

If you’re running a large WordPress site with hundreds or thousands of posts, comments and tags, there’s a fair chance it’s running slowly. Sometimes this is a hosting issue, other times a poorly coded plugin or theme, but occasionally you’re doing everything right and it’s WordPress that’s letting you down.

What is SQL_CALC_FOUND_ROWS and why’s it bad?

SQL_CALC_FOUND_ROWS is an older MySql function used for returning the total amount of rows found for a database query. You need to know the total amount of rows returned by a query in order to calculate the amount of pages need for pagination. The problem is it’s quite an old function, isn’t terribly well optimised, and can be particularly inefficient and slow your database query right down. A simple google for “SQL_CALC_FOUND_ROWS” will reveal page upon page of complaints about speed and and comparisons between using it or running an secondary query instead using COUNT(*). The general consensus is that running a secondary query is often faster as COUNT(*) doesn’t bother loading the whole table.

Unfortunately there are a few places in WordPress that use SQL_CALC_FOUND_ROWS, pretty much all the big query classes do (WP_Query, WP_User_Query etc etc) and it shows no signs of going away any time soon.

How do I know if SQL_CALC_FOUND_ROWS is being slow

First thing to do is check if it is SQL_CALC_FOUND_ROWS slowing down your site. The easiest way to do this is by viewing all the database queries happening on page load and examining the execution time. Although this sounds complicated it’s actually quite easy to do. There are loads of plugins in the plugin directly that will list the your DB queries for each page load, I personally have used Query Monitor before to great success.

How to fix it, (Without Pagination)

If you’re not using any sort of pagination in your WP_Query you can just tell WordPress not to run the pagination database queries, and hence not SQL_CALC_FOUND_ROWS. All you have to do is pass the little known about no_found_rows variable to WP_Query.

To filter it out of your main posts query, add this to your functions.php file in your theme folder.

if ( ! function_exists( 'wpartisan_set_no_found_rows' ) ) :

	/**
	 * Sets the 'no_found_rows' param to true.
	 *
	 * In the WP_Query class this stops the use of SQL_CALC_FOUND_ROWS in the
	 * MySql query it generates.
	 *
	 * @param  WP_Query $wp_query The WP_Query instance. Passed by reference.
	 * @return void
	 */
	function wpartisan_set_no_found_rows( \WP_Query $wp_query ) {

		if ( $wp_query->is_main_query() ) {

			$wp_query->set( 'no_found_rows', true );

		}
	}
endif;
add_filter( 'pre_get_posts', 'wpartisan_set_no_found_rows', 10, 1 );

To filter it out of a custom query, simply pass it as an option.

$the_query = new WP_Query( array( 'no_found_rows' => TRUE) );

How to I fix it, (With Pagination)

The original code was based on a post by b0b_ on the WordPress support forums. Well it works it ignores any custom params that you may have passed into the main query. Below is a better version that should work no matter what params you add to any WP_Query.

if ( ! function_exists( 'wpartisan_set_no_found_rows' ) ) :

	/**
	 * Sets the 'no_found_rows' param to true.
	 *
	 * In the WP_Query class this stops the use of SQL_CALC_FOUND_ROWS in the
	 * MySql query it generates. It's slow so we're going to replace it with
	 * a COUNT(*) instead.
	 *
	 * @param  WP_Query $wp_query The WP_Query instance. Passed by reference.
	 * @return void
	 */
	function wpartisan_set_no_found_rows( \WP_Query $wp_query ) {
		$wp_query->set( 'no_found_rows', true );
	}
endif;
add_filter( 'pre_get_posts', 'wpartisan_set_no_found_rows', 10, 1 );

if ( ! function_exists( 'wpartisan_set_found_posts' ) ) :

	/**
	 * Workout the pagination values.
	 *
	 * Uses the query parts to run a custom count(*) query against the database
	 * then constructs and sets the pagination results for this wp_query.
	 *
	 * @param array    $clauses  Array of clauses that make up the SQL query.
	 * @param WP_Query $wp_query The WP_Query instance. Passed by reference.
	 * @return array
	 */
	function wpartisan_set_found_posts( $clauses, \WP_Query $wp_query ) {

		// Don't proceed if it's a singular page.
		if ( $wp_query->is_singular()  ) {
			return $clauses;
		}

		global $wpdb;

		// Check if they're set.
		$where = isset( $clauses[ 'where' ] ) ? $clauses[ 'where' ] : '';
		$join = isset( $clauses[ 'join' ] ) ? $clauses[ 'join' ] : '';
		$distinct = isset( $clauses[ 'distinct' ] ) ? $clauses[ 'distinct' ] : '';

		// Construct and run the query. Set the result as the 'found_posts'
		// param on the main query we want to run.
	 	$wp_query->found_posts = $wpdb->get_var( "SELECT $distinct COUNT(*) FROM {$wpdb->posts} $join WHERE 1=1 $where" );

		// Work out how many posts per page there should be.
		$posts_per_page = ( ! empty( $wp_query->query_vars['posts_per_page'] ) ? absint( $wp_query->query_vars['posts_per_page'] ) : absint( get_option( 'posts_per_page' ) ) );

		// Set the max_num_pages.
		$wp_query->max_num_pages = ceil( $wp_query->found_posts / $posts_per_page );

		// Return the $clauses so the main query can run.
		return $clauses;
	}
endif;
add_filter( 'posts_clauses', 'wpartisan_set_found_posts', 10, 2 );

The first hook just runs a function that sets ‘no_found_rows’ to true in the query to stop SQL_CALC_FOUND_ROWS from being added. The second hooks into a handy filter that runs just before WordPress runs the database query that contains all the query parts in. We reconstruct the query and run it ourselves but with a COUNT(*) and without any limits set. We when work out and set the pagination params on the WP_Query. Simples.

Now to work out how to do the same for WP_User_Query.