[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:  Future Release
Component:  Comments                    |     Version:  3.3
 Severity:  major                       |  Resolution:
 Keywords:  needs-testing dev-feedback  |     Focuses:  performance
----------------------------------------+-----------------------------

Comment (by Znuff):

 I've been scouring the whole trac for this particular issue, and I'm
 surprised that after all these years (this ticket is _8_ years old, #32366
 is "only" 4, and there are others that are around 5-6 years old).

 Once again, I am baffled that nobody really wants to tackle this issue and
 fix it in the core.

 Right now I am working on a large-ish site with around ~1mil comments, and
 I've stumbled upon this specific part of the dashboard.

 More specifically, I'm surprised by this piece of code:

 {{{#!php
 <?php
 /**
  * Add edit comments link with awaiting moderation count bubble.
  *
  * @since 3.1.0
  *
  * @param WP_Admin_Bar $wp_admin_bar
  */
 function wp_admin_bar_comments_menu( $wp_admin_bar ) {
   if ( ! current_user_can( 'edit_posts' ) ) {
     return;
   }

   $awaiting_mod  = wp_count_comments();
   $awaiting_mod  = $awaiting_mod->moderated;
   $awaiting_text = sprintf(
     /* translators: %s: number of comments in moderation */
     _n( '%s Comment in moderation', '%s Comments in moderation',
 $awaiting_mod ),
     number_format_i18n( $awaiting_mod )
   );

   $icon   = '<span class="ab-icon"></span>';
   $title  = '<span class="ab-label awaiting-mod pending-count count-' .
 $awaiting_mod . '" aria-hidden="true">' . number_format_i18n(
 $awaiting_mod ) . '</span>';
   $title .= '<span class="screen-reader-text comments-in-moderation-
 text">' . $awaiting_text . '</span>';

   $wp_admin_bar->add_menu(
     array(
       'id'    => 'comments',
       'title' => $icon . $title,
       'href'  => admin_url( 'edit-comments.php' ),
     )
   );
 }
 }}}

 So `wp_count_comments()` gets called only for one thing and one thing in
 particular in this case: getting the moderated (pending) comments. This
 seems to be a rather wasteful query, when you could simply do:

 {{{#!php
 <?php
 global $wpdb;
 $awaiting_mod  = $wpdb->get_var("SELECT comment_approved, count(*) as
 total from $wpdb->comments where comment_approved='0';");
 }}}

 And shave around 99% of the query time.

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