![(please configure the [header_logo] section in trac.ini)](/chrome/site/your_project_logo.png){kind=logo}
#57168 closed defect (bug) (duplicate)
Comments count queries are sometimes way too slow due to needless order clause.
| Reported by: | Guss77 | Owned by: | |
|---|---|---|---|
| Priority: | normal | Milestone: | |
| Component: | Comments | Version: | 6.1.1 |
| Severity: | normal | Keywords: | |
| Cc: | Focuses: | performance |
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.