WordPress.org

Make WordPress Core

Opened 4 years ago

Closed 4 years ago

Last modified 4 years ago

#16846 closed defect (bug) (fixed)

wp_dashboard_recent_comments can generate hundreds of queries

Reported by: ocean90 Owned by:
Milestone: 3.1.1 Priority: high
Severity: normal Version: 3.0
Component: Comments Keywords: has-patch
Focuses: Cc:

Description

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

Attachments (3)

16846.patch (1005 bytes) - added by ocean90 4 years ago.
16846-2.patch (1.6 KB) - added by azaozz 4 years ago.
Includes the patch by ocean90, bypasses the "can read" check for users that can moderate comments
db.txt (96.8 KB) - added by robertaccettura 4 years ago.
DB Query Dump

Download all attachments as: .zip

Change History (17)

@ocean904 years ago

comment:1 @ocean904 years ago

  • Version changed from 3.1 to 3.0

comment:2 @scribu4 years ago

  • Keywords reporter-feedback added

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?

Version 0, edited 4 years ago by scribu (next)

comment:3 @ocean904 years ago

  • Keywords reporter-feedback removed

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

comment:4 @azaozz4 years ago

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.

@azaozz4 years ago

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

comment:5 @ocean904 years ago

  • Keywords 3.2-early removed
  • Milestone changed from Future Release to 3.2

comment:6 @nacin4 years ago

  • Milestone changed from 3.2 to 3.1.1

comment:7 @ryan4 years ago

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

comment:8 @azaozz4 years ago

  • Resolution set to fixed
  • Status changed from new to closed

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

comment:9 @azaozz4 years ago

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

comment:10 @azaozz4 years ago

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

comment:11 follow-up: @robertaccettura4 years ago

  • Cc robert@… added

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.

comment:12 in reply to: ↑ 11 @azaozz4 years ago

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.

@robertaccettura4 years ago

DB Query Dump

comment:13 @robertaccettura4 years ago

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

comment:14 @nacin4 years ago

That's actually this bug: #14222.

Note: See TracTickets for help on using tickets.