In phpmyadmin I often see this query that hangs with a status of "sending data":
SELECT SQL_CALC_FOUND_ROWS wp2a_posts.ID FROM wp2a_posts WHERE 1=1 AND wp2a_posts.post_author IN (1) AND wp2a_posts.post_type = 'post' AND (wp2a_posts.post_status = 'publish') ORDER BY wp2a_posts.post_date DESC LIMIT 0, 3
I would like to disable this or do whatevers necessary so that these queries don't need to be run, as my apache status keeps filling up with connections stuck on 'sending reply' and my websites constantly suffer downtime, and this may be the primary reason.
According to How to disable `SQL_CALC_FOUND_ROWS` WP_Query needs to be modified to fix this so the SQL_CALC_FOUND_ROWS query isn't used, but I have no idea where to edit WP_Query or see it referenced anywhere in my theme files; Query causing load because of SQL_CALC_FOUND_ROWS post counting? also gives no definitive answer. According to https://wpartisan.me/tutorials/wordpress-database-queries-speed-sql_calc_found_rows the following code can be put in functions.php of the active theme to stop it:
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 );
I tried this and the SQL_CALC_FOUND_ROWS queries still keep showing up. What else can I do to make it so that the SQL_CALC_FOUND_ROWS queries don't run?
SQL_CALC_FOUND_ROWS. But as the answer mentioned,no_found_rowsshould be disabled on a per-query basis, i.e. when pagination is not needed.no_found_rowsshould be enabled"100or even999). But for all other queries, particularly those made by plugins and themes, you'd need to trace the source (code and file) and depending on whether the code suppresses filters (e.g. using'suppress_filters' => true), you may be able to enable theno_found_rowsusing the same code in question or using the specific plugin/theme filter, if any..queryhook/filter which you can use to remove theSQL_CALC_FOUND_ROWS.. but that would affect all queries and not just those made byWP_Query. Additionally, the docs also stated, "Some queries are made before the plugins have been loaded, and thus cannot be filtered with this method.".