wp_dashboard_recent_comments can generate hundreds of queries

[ocean90]

Reported by a good friend.

How to reproduce it:

You need a site with > 4000 comments. Now go to the dashboard, recent comments widget and change the number of comments to show too 3.

What will happen:

You get a slowly backend because of ~100 queries more.

How to fix it:

/wp-admin/includes/dashboard.php line 612, instead of the static number 5 it should be $total_items.

Related: r16922

[scribu]

By your description, it would mean that each extra comment adds an extra 50 queries.

What happens if you set the widget to display 10 comments instead of 3?

[ocean90]

Site with 5,439 comments.

Number of queries without the fix:

• Default (5 comments): 46
• 4 comments: 156
• 3 comments: 155
• 2 comments: 154
• 1 comment: 153
• 0 comments: 152 (Yeah, it's possible, but you will get the message No comments yet.)
• 10 comments: 51

The query; X is from 0 to 5500, intervall is 50.

SELECT * FROM wp_comments c LEFT JOIN wp_posts p ON c.comment_post_ID = p.ID WHERE p.post_status != 'trash' ORDER BY c.comment_date_gmt DESC LIMIT X, 50

Number of queries with the fix:

• Default (5 comments): 46
• 4 comments: 46
• 3 comments: 45
• 2 comments: 44
• 1 comment: 43
• 0 comments: 41
• 10 comments: 51

[azaozz]

The patch makes sense. When the displayed number of comments is less than 5 the inner foreach breaks but the while continues to run and fetches more comments.

However there is some more room for optimization: looking at the next line we check if the user can read the comment's post current_user_can( 'read_post', $comment->comment_post_ID ). Think we can safely bypass that check for admin users (the most common case?) as they should be able to read all posts. That would reduce the number of queries further.

[azaozz]

Includes the patch by ocean90, bypasses the "can read" check for users that can moderate comments

[ryan]

Tested 16846-2.patch with counts < 5, > 5, and 5. It eliminates the extra queries and produces the same results.

[azaozz]

(In [17556]) Fix discrepancy in the nested loop in the Recent Comments dashboard widget, props ocean90, fixes #16846 for trunk

[azaozz]

(In [17557]) Fix discrepancy in the nested loop in the Recent Comments dashboard widget, props ocean90, fixes #16846 for 3.1.1

[azaozz]

Perhaps we can handle/review/fix the improvements from 16846-2.patch later.

[robertaccettura]

I've been having this problem for a while. On 3.1.1 I don't see any real improvement. 23,104 comments reported (and that's how many I see in wp_comments, so I presume that includes spam, which IMHO is another bug in regards to comment count).

Perhaps just a wp-config flag to disable this statistic? IMHO not worth the slowness for what it offers.

[azaozz]

Replying to robertaccettura:

Perhaps just a wp-config flag to disable this statistic? IMHO not worth the slowness for what it offers.

This ticket was specifically for the Recent Comments dashboard widget and fixed an inconsistency in the nested loop there when the user chooses to show less than 5 comments.

If you're experiencing slowness in the comments count functions, please open a new one.

[robertaccettura]

DB Query Dump

[robertaccettura]

Just attached a dump of the queries. This seems like the same bug.

[nacin]

That's actually this bug: #14222.