Make WordPress Core

Opened 17 months ago

Closed 11 months ago

Last modified 11 months ago

#57168 closed defect (bug) (duplicate)

Comments count queries are sometimes way too slow due to needless order clause.

Reported by: guss77's profile Guss77 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 = '';

Change History (3)

#1 @sabernhardt
17 months ago

  • Component changed from General to Comments
  • Focuses performance added

#2 @Guss77
11 months ago

  • Resolution set to duplicate
  • Status changed from new to closed

Duplicate of #58368.

#3 @SergeyBiryukov
11 months ago

  • Milestone Awaiting Review deleted
Note: See TracTickets for help on using tickets.