[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:  enhancement  |     Status:  new
 Priority:  normal       |  Milestone:  Awaiting Review
Component:  Comments     |    Version:  4.1
 Severity:  normal       |   Keywords:
  Focuses:  performance  |
-------------------------+-----------------------------
 Not sure if it's related to #22301, but I sniped a query running when the
 Dashboard is running on a busy site with 810k entries in the wp_comments
 table.

 The unoptimized query is as follows:
 {{{
 SELECT
         comment_approved,
         COUNT(*) AS num_comments
 FROM
         wp_comments
 WHERE
         comment_type != 'trackback'
 AND comment_type != 'pingback'
 GROUP BY
         comment_approved
 }}}

 The query doesn't use an index and ends up in the nasty `Using where;
 Using temporary; Using filesort`. This query runs in 3+ seconds for me.

 The following index resolves the issue, though the query is still
 relatively slow (0.6s, but this timing is much more consistent across
 runs, even with SQL_NO_CACHE): (`comment_approved`, `comment_type`). Now
 the query uses a much less nasty `Using where; Using index`.

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