Slow "SELECT SQL_CALC_FOUND_ROWS" SQL queries

When you investigate slowness in WordPress, you may find queries with this structure:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
LEFT JOIN wp_postmeta
ON ( wp_posts.ID = wp_postmeta.post_id )
...

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
INNER JOIN wp_postmeta
ON ( wp_posts.ID = wp_postmeta.post_id )
WHERE 1=1
...

The fragments above are illustrative, not complete queries to run. SQL_CALC_FOUND_ROWS calculates the total number of rows matching the filters even when the query returns only one page of results. It can appear with various tables, not only posts and postmeta.

As tables grow, a query that examines too many rows can take longer and become a bottleneck. The effect depends on the data and the execution plan.

The presence of SQL_CALC_FOUND_ROWS does not identify the cause on its own. The cost can depend on the filters, a JOIN on metadata, the number of results, the ordering applied or the indexes that already exist. First find the plugin, theme or request that generates the query using PHP X-Ray.

Before creating an index, keep a recoverable backup and run EXPLAIN on the complete query. Also check the table's current indexes:

SHOW INDEX FROM wp_postmeta;

A composite index such as (post_id, meta_key) can help certain execution plans, but it is not a universal solution and it adds cost to every write. If the analysis confirms that it is missing, use a maintained tool such as Index WP MySQL For Speed or ask the developer to design and test the index on a clone.

After the change, run EXPLAIN again, compare the time over several runs and check inserts, searches, the admin area and scheduled tasks. Revert the index if it does not improve the plan or if it slows down other operations.

If the code does not need pagination

In a custom query built with WP_Query, the developer can consider no_found_rows => true when neither the total nor the number of pages is used. Do not apply it globally: it would break features that do depend on those values. The WP_Query reference describes the parameter.

Also review the filters, ordering and number of results requested. Do not edit the WordPress core or remove the clause from a generated query just because it appears in the report. Validation must check results and pagination, as well as time.