[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:  Future Release
Component:  Administration          |     Version:  3.0
 Severity:  normal                  |  Resolution:
 Keywords:  has-patch dev-feedback  |
------------------------------------+-----------------------------
Changes (by SergeyBiryukov):

 * keywords:  has-patch needs-refresh dev-feedback => has-patch dev-feedback


Comment:

 One problem here is that current query (introduced in [11749]) fetches not
 only comments, but also full rows of parent posts, which seems unnecessary
 for this widget. With a lot of spam comments to skip, this makes the
 queries about 3 times slower. [attachment:14222.3.patch] fixes just that.

 [attachment:14222.4.patch] is a refresh of 14222.2.patch with a couple of
 changes:
 1. `comment_approved` values need to be compared as strings, not integers.
 2. Includes 14222.3.patch.

 Test 1: 10,303 comments total (3 approved + 10,000 spam + 300 trash)
 {{{
 WP 3.2.1
 Total Queries: 234, Total query time: 347,613.0 ms
 SELECT * FROM trunk_comments c LEFT JOIN trunk_posts p ON
 c.comment_post_ID = p.ID WHERE p.post_status != 'trash' ORDER BY
 c.comment_date_gmt DESC LIMIT 0 , 50
 0.7350 sec

 14222.3.patch
 Total Queries: 234, Total query time: 107,065.7 ms
 SELECT c.* FROM trunk_comments c LEFT JOIN trunk_posts p ON
 c.comment_post_ID = p.ID WHERE p.post_status != 'trash' ORDER BY
 c.comment_date_gmt DESC LIMIT 0 , 50
 0.1640 sec

 14222.4.patch
 Total Queries: 27, Total query time: 38.1 ms
 SELECT c.* FROM trunk_comments c LEFT JOIN trunk_posts p ON
 c.comment_post_ID = p.ID WHERE c.comment_approved IN ('0','1') AND
 p.post_status != 'trash' ORDER BY c.comment_date_gmt DESC LIMIT 0, 5
 0.0006 sec
 }}}
 Test 2: 10,303 comments total (10,003 approved + 300 trash)
 {{{
 WP 3.2.1
 Total Queries: 38, Total query time: 1,994.9 ms
 SELECT * FROM trunk_comments c LEFT JOIN trunk_posts p ON
 c.comment_post_ID = p.ID WHERE p.post_status != 'trash' ORDER BY
 c.comment_date_gmt DESC LIMIT 0 , 50
 0.8101 sec

 14222.3.patch
 Total Queries: 38, Total query time: 437.3 ms
 SELECT c.* FROM trunk_comments c LEFT JOIN trunk_posts p ON
 c.comment_post_ID = p.ID WHERE p.post_status != 'trash' ORDER BY
 c.comment_date_gmt DESC LIMIT 0 , 50
 0.1614 sec

 14222.4.patch
 Total Queries: 38, Total query time: 438.2 ms
 SELECT c.* FROM trunk_comments c LEFT JOIN trunk_posts p ON
 c.comment_post_ID = p.ID WHERE c.comment_approved IN ('0','1') AND
 p.post_status != 'trash' ORDER BY c.comment_date_gmt DESC LIMIT 0, 5
 0.1740 sec
 }}}

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