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.

[callumbw95]

Hi All,

Just took a look at this, and tested @donmhico's solution and can confirm that this works. Additionally to this I have written an extra filter to remove the comments button from the header entirely too as the aforementioned filter stops the comment counter from working.
To do this I have added the following filter:

<?php
function remove_comments_count_from_wp_admin_bar()
{
        global $wp_admin_bar;
        $wp_admin_bar->remove_menu('comments');
}
add_action('wp_before_admin_bar_render', 'remove_comments_count_from_wp_admin_bar');

You will still need @donmhico's filter included to stop the SQL still, but this additional filter is just a bit of polish if you didn't want the button to show in the admin bar.

Also, seeing as this ticket hasn't had any activity in the last 4 years, I am going to mark this ticket as closed. But for future reference as this doesn't specifically mention an issue or something within core WordPress, I would recommend submitting any site specific issues at the ​WordPress Support Forum, as you are more likely to get a quicker reply there for non core related issue.

Hope this helps 😃