[wp-trac] [WordPress Trac] #14711: Indexes for comment_author_email and user_id

[WordPress Trac]

#14711: Indexes for comment_author_email and user_id
--------------------------+-------------------------------------------------
 Reporter:  tellyworth    |       Owner:                   
     Type:  defect (bug)  |      Status:  new              
 Priority:  normal        |   Milestone:  3.1              
Component:  Comments      |     Version:  3.0              
 Severity:  normal        |    Keywords:  reporter-feedback
--------------------------+-------------------------------------------------

Comment(by josephscott):

 Code for this recently hit the Akismet WP plugin and it makes for queries
 that look like:

 {{{
 SELECT COUNT( * )
 FROM wp_comments
 WHERE user_id = 5
 AND comment_approved = 1
 }}}

 and:

 {{{
 SELECT COUNT( * )
 FROM wp_comments
 WHERE comment_author_email = 'joseph at josephscott.org'
 AND comment_author = 'Joseph Scott'
 AND comment_author_url = 'http://josephscott.org/'
 AND comment_approved = 1
 }}}

 The first is for comments left by users that were logged in, the second
 for those who weren't logged in.  Unfortunately both of these queries do
 full table scans.  Two new indexes that would help: user_id and
 comment_author_email (this seemed like the most likely to get a hit vs.
 author, author_url).

-- 
Ticket URL: <http://core.trac.wordpress.org/ticket/14711#comment:9>
WordPress Trac <http://core.trac.wordpress.org/>
WordPress blogging software