[wp-trac] [WordPress Trac] #31072: Slow comment-related query slowing down Dashboard

[WordPress Trac]

#31072: Slow comment-related query slowing down Dashboard
--------------------------+--------------------------
 Reporter:  archon810     |       Owner:
     Type:  defect (bug)  |      Status:  reopened
 Priority:  normal        |   Milestone:
Component:  Comments      |     Version:  4.1
 Severity:  normal        |  Resolution:
 Keywords:                |     Focuses:  performance
--------------------------+--------------------------
Changes (by rodrigosprimo):

 * status:  closed => reopened
 * resolution:  duplicate =>


Comment:

 I'm reopening this ticket after talking to @rachelbaker as I don't think
 it is a duplicate of #22301.

 I'm facing a very similar issue with a slightly different query that is
 significantly improved if a index is added to `comment_type` column.

 Plugins like WooCommerce or Easy Digital Downloads use custom comment
 types to store different kinds of data. They then use the
 `wp_count_comments` filter to exclude custom comment types from comments
 count. Using WooCommerce as an example, the resulting query to count
 comments is:

 {{{#!sql
 SELECT
     comment_approved, COUNT( * ) AS num_comments
 FROM
     wp_comments
 WHERE
     comment_type != 'order_note'
 GROUP BY
     comment_approved;
 }}}

 This query runs for every page that displays the admin top bar and, in one
 of my sites which has about 70k comments, it takes +5 seconds. Adding the
 index below makes the query run in 1.7 seconds, which is still slow but
 much better.

 {{{#!sql
 ALTER TABLE `wp_comments` ADD INDEX `idx_comment_type` (`comment_type`);
 }}}


 I'm not well versed in database performance improvements so I'm not sure
 what are the implications of this change and if there is another approach
 to solve this problem.

 Thoughts?

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