[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                          |  markjaquith
 Priority:  normal                               |      Status:  accepted
Component:  Comments                             |   Milestone:  Future
 Severity:  normal                               |  Release
 Keywords:  needs-testing dev-feedback needs-    |     Version:  3.3
  refresh                                        |  Resolution:
                                                 |     Focuses:
                                                 |  performance
-------------------------------------------------+-------------------------

Comment (by ComputerGuru):

 I was going through my old plugins and found one I'd made of this ticket
 many, many years ago. I've updated the code and verified that it remains
 consistent with the current behavior of wp_count_comments as of WP 4.4.2.

 I'm actually not sure about the WordPress policy when it comes to using
 MySQL UNIONs, but I've changed (at least for myself) the behavior of the
 patch to issue all 4 queries as one UNION ALL query to avoid the round-
 trip to the db.

 The query as it now stands:


 {{{
 (SELECT COUNT( comment_ID ) FROM wp_comments WHERE comment_approved =
 'trash') UNION ALL
 (SELECT COUNT( comment_ID ) FROM wp_comments WHERE comment_approved =
 'spam') UNION ALL
 (SELECT COUNT( comment_ID ) FROM wp_comments WHERE comment_approved = '0')
 UNION ALL
 (SELECT COUNT( comment_ID ) FROM wp_comments WHERE comment_approved =
 'post-trash') UNION ALL
 (SELECT COUNT( comment_ID ) FROM wp_comments);
 }}}

 I'm attaching the revised "plugin" to this ticket.

--
Ticket URL: <https://core.trac.wordpress.org/ticket/19901#comment:19>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform