#57168 closed defect (bug) (duplicate)
Comments count queries are sometimes way too slow due to needless order clause.
Reported by: |
|
Owned by: | |
---|---|---|---|
Milestone: | Priority: | normal | |
Severity: | normal | Version: | 6.1.1 |
Component: | Comments | Keywords: | |
Focuses: | performance | Cc: |
Description
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 = '';
Duplicate of #58368.