Optimize core queries at scale

Performance issues can be caused by core WordPress queries that perform well when a site has a small amount of data, but as the volume of site content increases the queries require a longer amount of time to execute.

The increased amount of time required by the queries can go unnoticed during normal amounts of site traffic, but when the amount of traffic increases—or a single bot crawls many uncached pages—the inefficiency of the queries can cause the site to experience performance issues.

For example, if a query targets a database table large enough to contain 700,000 published posts, sorting that volume of posts by date or category cannot be performed in memory. Instead, the database must use a filesort. A filesort copies the list of posts to a temporary table in a physical file on disk and then a sort is run on the file. This process can be very slow, and it can slow down other database operations that also need to use a filesort.

Sorting operations are often the most expensive part of a query. To make a query more efficient, the amount of data that is retrieved before the sort operation should be reduced as much as possible.

For example, if a query is intended to retrieve only the five most recent posts in the “News” category, sorting through all 700,000 published posts in the database is unnecessary. Instead, the query should be limited to a reasonable range of time that might include the five most recent posts from that category, and the results within that range can then be sorted (e.g. newest to oldest).

Analyze the efficiency of a query with EXPLAIN

The MySQL EXPLAIN statement provides more detailed information about how SELECT, DELETE, INSERT, REPLACE, and UPDATE statements are executed by MySQL. The information returned by EXPLAIN can help to reveal the inefficient aspects of a database query that can and should be optimized.

As an example, using an EXPLAIN statement to analyze an example query that is intended to retrieve the five most recent posts in the “News” category reveals that the operation is sorting through 677,527 rows:

In this example, the EXPLAIN statement also reveals that there is an index named type_status_date on the posts table (for type + status + date), but that index was not used to narrow down the recent posts. In its current state, the query is using the term_taxonomy_id index in wp_term_relationships to retrieve all posts in the “News” category, sifting them (by type and status), then sorting them by date. This order of operations is expensive and slow to perform on such an enormous amount of data.

Optimize the query

If a date constraint is added to the query in code—for example by limiting the query to only posts published in the last week—the SQL query will use the post index type_status_date first. Running the operation against the type_status_date index first for a specific range of time will greatly reduce the number of rows to sort through and retrieve posts that match the post type and the publish status. The term_taxonomy_id index can then be used to identify the posts in the “News” category and select the most recent five.

An example of the EXPLAIN statement for the improved version of the query reveals that now only 5,825 rows are being sorted:

This improved version of the query, even if a temporary table is used, requires much less disk space and time to sort a much smaller number of items.

Pagination queries and SQL_CALC_FOUND_ROWS performance

SQL_CALC_FOUND_ROWS is a MySQL query modifier that calculates the total number of rows that will be returned by a query without the LIMIT clause. In WordPress, the total number of found rows is then retrieved by FOUND_ROWS() to provide the necessary data for pagination calculations.

For large datasets, SQL_CALC_FOUND_ROWS can result in slower queries and degraded site performance, particularly during high traffic events. References to SQL_CALC_FOUND_ROWS will appear in an environment’s Slow Query Logs or in Query Monitor if it has contributed to degraded performance.

In WordPress Core SQL_CALC_FOUND_ROWS is enabled by default in WP_Query, but can be disabled in code if pagination is not needed.

Disable pagination

If pagination is not required for a WordPress site, SQL_CALC_FOUND_ROWS can be disabled with the no_found_rows argument.

In this code example, no_found_rows argument is set to true in a custom new WP_Query:

$args = new WP_Query( array(
    'post_type'           => 'post',
    'posts_per_page'      => 10,
    'no_found_rows'       => true, // Disable SQL_CALC_FOUND_ROWS
	'post_status'         => 'publish',
	'ignore_sticky_posts' => true,
) );

$posts = new WP_Query( $args );

Disable pagination for core queries

The no_found_rows argument can also be used to selectively code-disable SQL_CALC_FOUND_ROWS for core queries.

In this code example, pagination queries are disabled on core queries on the single post page:

function disable_pagination_on_single_pages( $query ) {
    // Check if it's the main query, on the front end, and a single post/page
    if ( $query->is_main_query() && !is_admin() && ( $query->is_singular() ) ) {
        // Disable the SQL_CALC_FOUND_ROWS query
        $query->set( 'no_found_rows', true );
    }
}
add_action( 'pre_get_posts', 'disable_pagination_on_single_pages' );

Alternate pagination queries

If pagination is required—but SQL_CALC_FOUND_ROWS needs to be avoided to improve site performance—alternative methods can be used to perform the calculations for pagination. For example, found_posts_query or found_users_query can be used to replace SQL_CALC_FOUND_ROWS with a more efficient COUNT(*) query.

In this code example SQL_CALC_FOUND_ROWS is replaced by found_users_query to perform a user count query:

function alternative_user_found_rows( $value, $user_query ){
  $sql = "SELECT COUNT(*) AS TOTAL $user_query->query_from $user_query->query_where";
  return $sql;
}

add_filter( 'found_users_query', 'alternative_user_found_rows', 10, 2 );

Avoid duplicate queries

Duplicate queries should be avoided whenever possible. A common mistake that causes a duplicate query is using the get_posts() method to retrieve the posts property from a WP_Query object.

In this code example, a new WP_Query object is instantiated:

$args = [
  'posts_per_page'      => '10',
  'post_type'           => 'post',
  'post_status'         => 'publish',
  'no_found_rows'       => true,
  'ignore_sticky_posts' => true,
  'cat'                 => 123
];
$my_query = new WP_Query( $args );

If get_posts() is called to retrieve posts outside of the constructor, it will result in a second, duplicate query and negatively affect performance:

/* ❌ This approach is incorrect. */
$my_posts = $my_query->get_posts();

Instead, the posts should be retrieved by accessing the posts property of the WP_Query object:

/* ✅ This approach is correct. */
$my_posts = $my_query->posts;