[wp-trac] [WordPress Trac] #19901: Speeding up Dashboard and Comment moderation SQL load

[WordPress Trac]

#19901: Speeding up Dashboard and Comment moderation SQL load
-------------------------+-------------------------------------------------
 Reporter:  FolioVision  |      Owner:
     Type:  enhancement  |     Status:  new
 Priority:  normal       |  Milestone:  Awaiting Review
Component:  Performance  |    Version:  3.3.1
 Severity:  critical     |   Keywords:  wp_count_comments sql dashboard
                         |  speed performance
-------------------------+-------------------------------------------------
 The standard Wordpress function for counting the comments for Admin Bar
 and Dashboard named wp_count_comments is using a single SQL query with
 GROUP BY clause. That makes it slow on a large site with hundreds of
 thousands of comments.

 {{{
 SELECT comment_approved, COUNT(*) AS num_comments FROM wp_comments GROUP
 BY comment_approved;
 }}}
 This takes 0.3 seconds on our site with 400,000 comments. When there are
 10 editors logged in, we can see increasing server load.

 Our solution is to run 5 faster queries instead:

 {{{
 SELECT COUNT( comment_ID ) FROM wp_comments WHERE comment_approved =
 'trash'
 SELECT COUNT( comment_ID ) FROM wp_comments WHERE comment_approved =
 'spam'
 SELECT COUNT( comment_ID ) FROM wp_comments WHERE comment_approved = '0'
 SELECT COUNT( comment_ID ) FROM wp_comments WHERE comment_approved =
 'post-trash'
 SELECT COUNT( comment_ID ) FROM wp_comments
 }}}
 Takes 0.042144 on the same site. The last query gets the number of all the
 comments, then we subtract the previous query totals to get number of
 approved comments.

 On a database of 4 million comments the difference is 1.52 seconds for the
 original wp_count_comments and 0.01 seconds for our alternative count with
 5 queries.

 Here is a link to our quick piece of code which hooks to the required
 filter hook and replaces the original slow function wp_count_comments:
 http://foliovision.com/downloads/fv_wp_count_comments.php.txt

 But this is a hack - it would be much better to fix this in core by
 replacing the existing slow queries with 5 fast ones and subtraction to
 get total approved comments.

 This speedup can be very important on large sites, as often there are 10
 or more writers and moderators working at the same time. What can happen
 with the existing code is that the slow count comments query can back up
 MySQL and then writers can no longer save or open posts to edit. They get
 very, very frustrated and even angry.

 This fix will allow Wordpress to scale much larger on relatively modest
 hardware (no separate MySQL dual quad server).

 Thanks for listening.

 Martin

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/19901>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software