[wp-trac] [WordPress Trac] #14222: Improve dashboard recent comments widget performance by not fetching spam comments

[WordPress Trac]

#14222: Improve dashboard recent comments widget performance by not fetching spam
comments
-------------------------------------+------------------
 Reporter:  Viper007Bond             |       Owner:
     Type:  defect (bug)             |      Status:  new
 Priority:  normal                   |   Milestone:  3.2
Component:  Administration           |     Version:  3.0
 Severity:  normal                   |  Resolution:
 Keywords:  has-patch needs-testing  |
-------------------------------------+------------------

Comment (by technosailor):

 Replying to [comment:10 nacin]:
 > So we should either copy or move that to != 'spam', or NOT IN for both
 trash and spam, which shouldn't be much slower. There's going to be more
 spam comments than trash comments on pretty much every site, as spamming
 is going to be automated (and in far greater volumes), so I'm strongly
 inclined to deal with this one way or another now.

 I just ran benchmarks on a blog running locally (pristine trunk) having
 46508 rows in the comments table. I've disabled the query cache just to be
 safe.

 With the current query, it took 18.8 ms:

 {{{
 `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
 0, 50`
 }}}

 Changing the != to NOT IN() resulted in 19.2ms:

 {{{
 `SELECT * FROM wp_comments c LEFT JOIN wp_posts p ON c.comment_post_ID =
 p.ID WHERE p.post_status NOT IN ('trash') ORDER BY c.comment_date_gmt DESC
 LIMIT 0, 50`
 }}}

 Adding 'spam' in the NOT IN() results in 18.8ms:

 {{{
 `SELECT * FROM wp_comments c LEFT JOIN wp_posts p ON c.comment_post_ID =
 p.ID WHERE p.post_status NOT IN ('trash','spam') ORDER BY
 c.comment_date_gmt DESC LIMIT 0, 50`
 }}}


 I don't really think we're gaining any major advantage from this but happy
 to be wrong.

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/14222#comment:11>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software