WordPress Comments Core Query

[Uranbold]

Hello,

Issue: We have over 400K+ posts and I saw this slow query on any WordPress area on each page.

We had 2M+ comments.

Question: This Query is working on all pages on Dashboard or Settings, Plugin section and any sections why does it? It should works on only all Posts list?

Solution: I have noticed that Header Bar has Comments Icon and that has displayed the Pending Comments count. I have tried to disable it via custom Filter for following.

function admin_bar_remove_comments(){
        global $wp_admin_bar;
        $wp_admin_bar->remove_menu('comments');
}
add_action( 'wp_before_admin_bar_render', 'admin_bar_remove_comments' );

Slow Query:

SELECT comment_approved, COUNT( * ) AS total 
FROM wp_comments 
GROUP BY comment_approved

Environment Information:

WP Version 5.0.3 (also tested 4.9.8)
Theme: Twenty Seventeen (other themes)
Plugins: Query Monitor

Thanks.

[swissspidy]

Are you using some sort of object cache on your site? Because the comment count would be cached in that case. Plus, with your amount of data you really should think about leveraging an external object cache.

Plus, you can hook into the wp_count_comments filter to prevent that SQL query from running.

I don't see how else we could drastically improve that query in a way that it's worth doing.

[Uranbold]

Hello,

I have checked this with Fresh Multisite Network Installation too. But we had still same Query.

Could you please give me more information about the Leveraging an external object cache.

How do i hook this and prevent SQL query from running?

We have manually Removed the comment.php for get_comment_count().

Thanks.

[davidbaumwald]

Related #32366

[SergeyBiryukov]

Related: #19901

[donmhico]

@Uranbold regarding to hooking into wp_count_comments to prevent the SQL query from running, you can just use something like this.

<?php
function bypass_count_comments_sql( $post_id ) {
        return (object) array(
                'approved'       => 0,
                'moderated'      => 0,
                'spam'           => 0,
                'trash'          => 0,
                'post-trashed'   => 0,
                'total_comments' => 0,
                'all'            => 0,
        );
}
add_filter( 'wp_count_comments', 'bypass_count_comments_sql' );

If you look here - https://core.trac.wordpress.org/browser/tags/5.7/src/wp-includes/comment.php#L1434 - the code above will return $filtered; preventing get_comment_count( $post_id ); which invokes the query.