[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:  peterwilsoncc
     Type:  defect (bug)  |      Status:  assigned
 Priority:  normal        |   Milestone:  6.3
Component:  Comments      |     Version:  6.2.2
 Severity:  minor         |  Resolution:
 Keywords:  has-patch     |     Focuses:  performance
--------------------------+----------------------------

Comment (by peterwilsoncc):

 Running some EXPLAINs on MySQL 8.0.30-ubuntu using the innoDB engine:

 For the count queries, the query optimizer is discarding the orderby
 clause provided by WP and defaulting to it's preference:

 {{{#!sql
 EXPLAIN SELECT COUNT(*)
 FROM wp_comments
 WHERE ( ( comment_approved = '0'
 OR comment_approved = '1' ) )
 ORDER BY wp_comments.comment_date_gmt DESC;
 }}}

 It actually uses the `comment_approved_date_gmt` index because that makes
 more sense according to the WHERE clause.

 For the queries determining if the post type is used that include `LIMIT
 0,1` then removing the orderby clause does make a difference:

 {{{#!sql
 EXPLAIN SELECT wp_comments.comment_ID
 FROM wp_comments
 WHERE ( ( comment_approved = '0'
 OR comment_approved = '1' ) )
 AND comment_type IN ('', 'comment')
 ORDER BY wp_comments.comment_date_gmt DESC
 LIMIT 0,1;
 }}}

 The above uses the `comment_date_gmt` index, which isn't ideal for the
 job. By removing the orderby it uses the `comment_approved_date_gmt` index
 which seems a better fit.

 My inclination is to put the change in my PR in for the comment type
 dropdown (ie, the `LIMIT 0,1` queries) as I think that is where any
 benefit from this ticket will come from. For the counting queries, I don't
 think the changes actually help due to the optimizer.

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