WordPress has long used SQL_CALC_FOUND_ROWS for count statistics and pagination calculations, but this query statement is very slow when dealing with large data volumes (W+). A better solution is to replace it with a more modern COUNT statement.
Although this issue was reported to the official WordPress team via the WordPress Ticket System several years ago, it appears to have remained unresolved due to legacy reasons. (This is normal; there are many similar unresolved tickets.)
Below is a plugin written to temporarily resolve this issue until an official fix is released:
<?PHP
/**
* Plugin Name: Fix WordPress Slow Queries
* Description: Fix WordPress Slow Queries
* Author: Mahdi Akrami
* Version: 1.0.0
*/
class FIX_WP_SLOW_QUERY {
public static function init () {
/**
* WP_Query
*/
add_filter ( 'found_posts_query', [ __CLASS__, 'add_found_rows_query' ], 999, 2 );
add_filter ( 'posts_request_ids', [ __CLASS__, 'remove_found_rows_query' ], 999 );
add_filter ( 'posts_pre_query', function ( $posts, \WP_Query $query ) {
$query->request = self::remove_found_rows_query ( $query->request );
return $posts;
}, 999, 2 );
add_filter ( 'posts_clauses', function ( $clauses, \WP_Query $wp_query ) {
$wp_query->fw_clauses = $clauses;
return $clauses;
}, 999, 2 );
}
public static function remove_found_rows_query ( $sql ) {
return str_replace ( ' SQL_CALC_FOUND_ROWS ', '', $sql );
}
public static function add_found_rows_query ( $sql, WP_Query $query ) {
global $wpdb;
$distinct = $query->fw_clauses['distinct'] ?? '';
$join = $query->fw_clauses['join'] ?? '';
$where = $query->fw_clauses['where'] ?? '';
$groupby = $query->fw_clauses['groupby'] ?? '';
$count = 'COUNT (*)';
if ( ! empty ( $groupby ) ) {
$count = "COUNT ( distinct $groupby )";
}
return "
SELECT $distinct $count
FROM {$wpdb->posts} $join
WHERE 1=1 $where
";
}
}
FIX_WP_SLOW_QUERY::init ();