[wp-trac] [WordPress Trac] #26858: Comment duplicate check has a slow query

[WordPress Trac]

#26858: Comment duplicate check has a slow query
--------------------------+------------------------------------
 Reporter:  pento         |      Owner:
     Type:  defect (bug)  |     Status:  new
 Priority:  normal        |  Milestone:  3.9
Component:  Database      |    Version:  2.0
 Severity:  normal        |   Keywords:  has-patch dev-feedback
--------------------------+------------------------------------
 When checking for duplicate comments, the query is pretty slow:


 {{{
 SELECT comment_ID FROM wp_comments WHERE comment_post_ID = '1' AND
 comment_parent = '0' AND comment_approved != 'trash' AND ( comment_author
 = 'foo' OR comment_author_email = 'foo at bar.com' ) AND comment_content =
 'some content' LIMIT 1;
 }}}

 This will use the comment_post_ID index, which means it will scan all
 comments on that post. For a post with thousands of comments, this starts
 to get slow.

 Instead, we should change `KEY comment_post_ID (comment_post_ID)` to `KEY
 comment_post_ID (comment_post_ID,comment_content(255))`, to cover this
 case.

 Props [http://www.mysqlperformanceblog.com/2014/01/16/analyzing-wordpress-
 mysql-queries-query-analytics/ MySQL Performance Blog] for finding this.

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