How to optimize 'select found_rows()' query? Several 'high load average' alerts daily

Question

I have 5,000 regular posts, 6,000 posts in one post type and 2,000 posts in another post type. Needless to say, that has made the wp_posts table quite large. Not to mention, I have custom taxonomies set up to mimic the post types so for every Company A entered into Company post type, there is Company A, I manually entered Company A into Company taxonomy. That way, when I do a regular post about Company A, I can effectively tag Company A into the post and then that post appears on Company A's custom post type page.

Specs : Running WP Super Cache w/ Cloudflare as CDN. Theme is Wordpress TwentyEleven but heavily customized. Server: 2048 RAM, 80gb Raid, 8+CPU 4x priority, 5000GB Bandwidth. Traffic is 750k views/month & 200k uniques.

I'm starting to see performance issues increase pretty rapidly with two instances in the last week where I got an

error connecting to database

message on the site and had to restart mysql service. Not to mention, high load average emails several times a day.

Ran Debug queries. Ran it with WP Super Cache. Uninstalled WP Super Cache & tried W3 Total Cache w/ most of the options enabled. In both trials, I saw the longest load time caused by these two queries (but only on the first load! Almost 0 on subsequent loads):

Time: 7.79628753662E-5
Query: SELECT FOUND_ROWS()
Call from: require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/child-theme/index.php'), WP_Query->__construct, WP_Query->query, WP_Query->get_posts, WP_Query->set_found_posts, W3_Db->query, W3_DbCache->query, W3_DbCallUnderlying->query, W3_Db->query, W3_DbProcessor->query, W3_Db->default_query

Time: 6.29425048828E-5
Query: SET SESSION query_cache_type = 0;
Call from: require('wp-blog-header.php'), require_once('wp-includes/template-loader.php'), include('/themes/child-theme/index.php'), get_footer, locate_template, load_template, require_once('/themes/child-theme/footer.php'), wp_footer, do_action('wp_footer'), call_user_func_array, Debug_Queries->the_queries, Debug_Queries->get_queries, W3_Db->query, W3_DbCache->query, W3_DbCallUnderlying->query, W3_Db->query, W3_DbProcessor->query, W3_Db->default_query

I did remove an array which made it so that we looked for posts types in addition to regular posts but that didn't seem to make a difference.

Full example query output: http://pastebin.com/L0mSXe9q

Also, here is the template code for the main index:

    <?php if ( have_posts() ) : ?>

        <?php /* Start the Loop */ ?>

            <?php while ( have_posts() ) : the_post(); ?>

            <?php if ( 'status' == get_post_format() ) {

                get_template_part( 'content-status', get_post_format() );

            } else get_template_part( 'excerpt', get_post_format() );

        ?>

        <?php endwhile; ?>

Really not sure where to proceed from here.

Answer 1

This should not break pagination:

add_filter('pre_get_posts', 'optimized_get_posts', 100);
function optimized_get_posts() {
    global $wp_query, $wpdb;
    $wp_query->query_vars['no_found_rows'] = 1;
    $wp_query->found_posts = $wpdb->get_var( "SELECT COUNT(*) FROM wp_posts WHERE 1=1 AND wp_posts.post_type = 'post' AND (wp_posts.post_status = 'publish' OR wp_posts.post_status = 'private')" );
    $wp_query->found_posts = apply_filters_ref_array( 'found_posts', array( $wp_query->found_posts, &$wp_query ) );
    if($wp_query->query_vars['posts_per_page'] <= 0) {
        $wp_query->max_num_pages = 0;
    } else {
        $wp_query->max_num_pages = ceil($wp_query->found_posts / $wp_query->query_vars['posts_per_page']);
    }
    return $wp_query;
}

Answer 2

Just put this in your functions.php and you'll be fine.

add_filter( 'pre_get_posts', 'optimized_get_posts', 100 );
function optimized_get_posts() {

    global $wp_query;

    $wp_query->query_vars['no_found_rows'] = 1;

    return $wp_query;

}