-1

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

enter image description here

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?

  • Try installing the Query Monitor plugin and you can trace the source of the query having the SQL_CALC_FOUND_ROWS. But as the answer mentioned, no_found_rows should be disabled on a per-query basis, i.e. when pagination is not needed. – Sally CJ Nov 23 '19 at 2:59
  • I mean, "no_found_rows should be enabled" – Sally CJ Nov 23 '19 at 3:13
  • I am fine with stopping pagination completely everywhere if it makes it easier to solve this. So is it possible to enable no_found_rows for everything? – user178785 Nov 23 '19 at 3:19
  • For the main query, the code in question should work, but if it doesn't, try using a lower callback priority (i.e. a greater number like 100 or even 999). 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 the no_found_rows using the same code in question or using the specific plugin/theme filter, if any.. – Sally CJ Nov 23 '19 at 10:53
  • And there's actually the query hook/filter which you can use to remove the SQL_CALC_FOUND_ROWS.. but that would affect all queries and not just those made by WP_Query. Additionally, the docs also stated, "Some queries are made before the plugins have been loaded, and thus cannot be filtered with this method.". – Sally CJ Nov 23 '19 at 10:54
0

Using Query Monitor I was able to find the source of this query, it was from a plugin "starbox" which uses the query to fetch the last 3 posts from the author of a given post. Editing the plugin by commenting out the part it runs this query solved the issue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.