[wp-trac] [WordPress Trac] #57168: Comments count queries are sometimes way too slow due to needless order clause.

[WordPress Trac]

#57168: Comments count queries are sometimes way too slow due to needless order
clause.
--------------------------+-----------------------------
 Reporter:  Guss77        |      Owner:  (none)
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  General       |    Version:  6.1.1
 Severity:  normal        |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 The admin menu calls `wp_count_comments()` to count all the comments to
 show comment counts in the side bar. This triggers code in `wp-includes
 /class-wp-comment-query.php` to create a query that looks like this:

 {{{
 SELECT  COUNT(*)
                         FROM wp_xp7b48_comments
                         WHERE ( comment_approved = '1' )

                         ORDER BY wp_xp7b48_comments.comment_date_gmt DESC;
 }}}
 (spaces are as originally formatted formatting)

 This query is needlessly slow - on my system, with a burst-class VPS and
 ~5M comments, it often takes around 10 seconds. The database (MySQL
 5.7.40) problem appears to be the needless `ORDER BY` clause - just
 removing it speeds up the count query to under a half a second (there is
 an index on `comment_approved` so if the database doesn't need to sort by
 another column, it can just read the index, or even cache it).

 I work around this issue by adding, in `wp-includes/class-wp-comment-
 query.php` after line 715 (where `$fields` is being set to `'COUNT(*)'`),
 the following line:

 {{{
 $orderby = '';
 }}}

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