[wp-trac] [WordPress Trac] #31188: Very slow query in comments_template -> get_comments -> WP_Comment_Query->query

[WordPress Trac]

#31188: Very slow query in comments_template -> get_comments ->
WP_Comment_Query->query
--------------------------+-----------------------------
 Reporter:  archon810     |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  Awaiting Review
Component:  Comments      |    Version:  4.1
 Severity:  normal        |   Keywords:
  Focuses:  performance   |
--------------------------+-----------------------------
 In my quest to improve performance of core Wordpress functionality (see
 #31071,  #31072, and #31171), I'm back with another optimization that is
 quite significant for large WP installations with lots of comments on some
 posts.

 The query in question:
 {{{
 # Query_time: 25.314234  Lock_time: 0.000074 Rows_sent: 10  Rows_examined:
 699220
 SET timestamp=1422666393;
 SELECT wp_comments.* FROM wp_comments  WHERE comment_post_ID = '87814' AND
 comment_approved = '1'  ORDER BY comment_date_gmt DESC LIMIT 10;
 }}}

 As you can see, 25s is not great to say the least. The query isn't using
 an optimized index, which I've now created, at which point it started
 running in milliseconds.

 The index is as follows:
 {{{
 CREATE INDEX `comment_post_ID_approved_date_gmt` ON
 `wp_comments`(`comment_post_ID`, `comment_approved`, `comment_date_gmt`);
 }}}

 I've verified the performance improvements using SQL_NO_CACHE as well as
 EXPLAIN.

 The post in question here had over 3000 comments, which isn't uncommon on
 our site (androidpolice.com).

 Interestingly, MySQL queries using a different strategy internally which
 isn't nearly this slow for posts with fewer comments. But at some point,
 it switches it up because it thinks it's better, and things go sour.

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