[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                  |  Resolution:
 Keywords:  has-patch dev-feedback  |
------------------------------------+------------------

Comment (by nacin):

 Peter Zaitsev emailed me about this and I was looking into it earlier.
 Sounds good to me.

 What I'm wondering is if we could actually ditch the comment_content
 conditional. If we could query all comments against the post with one more
 limiting factor — an index on comment_author and/or comment_author_email —
 we might be able to avoid adding a one-off comment_content index (we don't
 do a query like this anywhere else). And then we'd pull all comments
 against a post, by that author, into PHP, and look for duplicate content
 that way.

 As we do an OR right now for comment_author and comment_author_email, a
 different index wouldn't actually help. I don't know how much sense this
 makes, though, and am trying to figure out the use case. Someone double-
 submits an identical comment, it gets caught, even if we compare author =
 % AND author_email = %s. If someone submits a comment, then goes back and
 changes *only* their name or email, it wouldn't get caught. I guess that
 kind of a correction is possible.

 There are a few other ugly comment queries (including another in that
 post, compounded by a plugin-added comment_subscribe field); perhaps it's
 worth looking into these for 3.9 and seeing if a few smart indexes could
 handle a lot of our problems.

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