[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 FolioVision):

 Hello @Znuff

 it's been 8 years, so lets do a quick check if this ticket makes sense.
 The web hosts have evolved and the SQL performance is much better with the
 SSD drives that with the spinning drives 8 years ago (or course that's not
 a reason to not run bad queries).

 Looking at the WordPress source for get_comment_count()
 https://github.com/WordPress/WordPress/blob/f545bb3f634c8e949db9051bd88924df592c3337
 /wp-includes/comment.php#L390-L397 I see it **still** runs:


 {{{
 SELECT comment_approved, COUNT( * ) AS total FROM wp_comments GROUP BY
 comment_approved;
 }}}

 When I run that without SQL cache

 {{{
 SELECT sql_no_cache comment_approved, COUNT( * ) AS total FROM
 wp_5_comments GROUP BY comment_approved;
 }}}

 I tested this again o 3 website:

 1) Website with 1,624,487.

 ...it takes 0.55 seconds.

 When I run all the individual queries as mentioned in the first post in
 this ticket I get times:

 * 0.01 for trash
 * 0.00 for spam
 * 0.00 for unapproved
 * 0.00 for post-trash
 * 0.00 for the total count

 So the difference is **huge**, although I have to admin there are not many
 comments in trash or spam.

 2) I tried on another site with 713,245 comments and that query with group
 by takes 0.31 s. Then with the individual queries I get these times:

 * 0.17 for trash
 * 0.00 for spam
 * 0.00 for unapproved
 * 0.00 for post-trash
 * 0.00 for the total count

 Which is also better.

 2) I tried on yet another site with  comments and that query with group by
 takes 0.13 s. Then with the individual queries I get these times:

 * 0.01 for trash
 * 0.00 for spam
 * 0.00 for unapproved
 * 0.00 for post-trash
 * 0.07 for the total count

 Which is a bit better.


 Still, my patch from 2 years ago was not accepted.

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