[wp-trac] [WordPress Trac] #58368: WordPress dashboard is very slow when there are many comments (and the database isn't great)

[WordPress Trac]

#58368: WordPress dashboard is very slow when there are many comments (and the
database isn't great)
--------------------------+-----------------------------
 Reporter:  Guss77        |      Owner:  (none)
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Comments      |    Version:  6.2.2
 Severity:  minor         |   Keywords:  has-patch
  Focuses:                |
--------------------------+-----------------------------
 It seems the admin dashboard - as far as I can tell in every page, so it
 must be something in the side bar - is calling `wp_count_comments()`. That
 causes the following database query to be run:

 {{{
 SELECT COUNT(*)
 FROM wp_xp7b48_comments
 WHERE ( comment_approved = '1' )
 ORDER BY wp_xp7b48_comments.comment_date_gmt DESC
 }}}

 ... among other queries that are as problematic, but as we have several
 million approved comments on the side, this is the one that is slow - it
 takes > 10 seconds on our (very tiny) server.

 The problem is that it doesn't have to be that way - there is
 **absolutely** no reason to have an `ORDER BY` clause in a `COUNT(*)`
 comment - that is just asking for the query to be needlessly slow.

 I can workaround the issue by editing `wp-includes/class-wp-comment-
 query.php` and in `get_comment_ids()` change:

 {{{
         $this->sql_clauses['orderby'] = $orderby;
 }}}

 to

 {{{
     if ( !$this->query_vars['count'] )
         $this->sql_clauses['orderby'] = $orderby;
 }}}

 This reduces the run time for `wp_count_comments()` from > 20 seconds less
 than 0.5 seconds.

 See attached screenshots from Query Monitor dev panel, showing slowness
 and results of workaround.

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/58368>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform