[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:  markjaquith
     Type:  enhancement  |      Status:  accepted
 Priority:  normal       |   Milestone:  3.4
Component:  Performance  |     Version:  3.3.1
 Severity:  critical     |  Resolution:
 Keywords:  needs-patch  |
-------------------------+--------------------------

Comment (by markjaquith):

 Replying to [comment:3 ryan]:
 > Plugins can set comment_status to anything. I think we'd need to
 introduce comment status registration, like we do with post status.

 Could do. But until then, it's not a big deal if the "approved" number
 contains any custom ones. We really don't support custom comment_approved
 statuses... I think I tried doing it once and there were multiple places
 where it broke. So I don't think that concern should hold us back on
 improving count performance.

 Replying to [comment:4 ryan]:
 > Is this with myisam or innodb?  innodb will count the rows and possibly
 be slower.
 > For the last query that doesn't have a WHERE.

 Tested on standard MyISAM, which is what we should be optimizing for. Can
 you test on one of the big wpcom InnoDB comment tables and post numbers?
 Be sure to query with {{{SELECT SQL_NO_CACHE ...}}} to disable qcache.

 Replying to [comment:6 ryan]:
 > Also, a hack I've seen done for sites with millions of comments and high
 comment volume is to just not do the count queries.  Comment counts become
 rather meaningless for such sites.  Maybe worth a throttle or don't care
 switch. Maybe not.

 We already have one:

 {{{
         $stats = apply_filters('wp_count_comments', array(), $post_id);
         if ( !empty($stats) )
                 return $stats;
 }}}

 Replying to [comment:7 coffee2code]:
 > Any performance difference in doing an `IN()` as opposed to separate
 queries?
 >
 > `SELECT COUNT( comment_ID ) FROM wp_comments WHERE comment_approved IN (
 'trash', 'spam', '0', 'post-trash' );`
 >
 > Could then throw a filter on the comment statuses prior to inclusion in
 the `IN()` so non-public comments can be omitted.  Which somewhat ties in
 with...

 That only gives you the total. We need the count for each comment_approved
 status.

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