Make WordPress Core

Opened 12 years ago

Closed 11 years ago

#22040 closed defect (bug) (invalid)

wp_dashboard_recent_comments queries do not skip comment_approved=spam

Reported by: _ck_'s profile _ck_ Owned by:
Milestone: Priority: normal
Severity: normal Version:
Component: Administration Keywords:
Focuses: performance Cc:

Description

There is am oversight in wp_dashboard_recent_comments which can cause a large delay in the admin area for large sites that may have a lot of spam (ie. set by akismet)

Ironically the function contains the documentation
"Select all comment types and filter out spam later for better query performance."

This is not the case, in fact it's the opposite. Filtering out the spam later via php and wp filters is far far slower since there is (now) an index on comment_approved.

As it is, it causes dozens of extra queries while it loops through all the comments 50 at a time in mysql when they contain a great deal of spam, and then via php filters the spam out (even for admin).

Since there IS an index on comment_approved that can be used, to fix this you should only show WHERE comment_approved='approved' OR comment_approved='moderated' Or do the reverse with comment_approved!='spam'

Since WP 3.5 has switched to a query class for comments, it should in theory be easy to add the additional WHERE condition for comment_approved.

Change History (8)

#1 follow-up: @nacin
12 years ago

  • Keywords reporter-feedback added

What version of WordPress are you running?

#3 in reply to: ↑ 1 ; follow-up: @_ck_
12 years ago

Replying to nacin:

What version of WordPress are you running?

.

I realize the query was changed after 3.2 but here's the thing - your default query in 3.4 and 3.5 is:

$comments_query = array( 'number' => $total_items * 5, 'offset' => 0 );

then class WP_Comment_Query { sets a default status of 'status' => '', which allows any status to be returned, so there is STILL no filtering on the comment_status

Then you still proceed to loop through the results via PHP instead of using mysql to filter.

Moderated posts are okay to return but spam should be excluded at the mysql level.

Unless akismet already filters the query. Which I do not see offhand in it's code and since the query is not named, I don't see how it could know to filter it in just in the dashboard box without a trigger.

So the resposibility of the dashboard box is to only fetch comment_approved = approved or moderated.

#4 in reply to: ↑ 3 @SergeyBiryukov
12 years ago

Related: [10079], [20609]

#5 @SergeyBiryukov
12 years ago

  • Keywords reporter-feedback removed

#7 @nacin
12 years ago

If status is empty when passed to WP_Comment_Query, the resulting query is comment_approved = '0' OR comment_approved = '1'. Untested, but this ticket appears to be invalid.

#8 @nacin
11 years ago

  • Component changed from Performance to Administration
  • Focuses performance added
  • Milestone Awaiting Review deleted
  • Resolution set to invalid
  • Status changed from new to closed
Note: See TracTickets for help on using tickets.