[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:     
--------------------------+-------------------------------------------------
Changes (by mdawaffe):

 * cc: mdawaffe (added)


Comment:

 What are the actual queries you'd want to do here?

 I imagine, for example, that you'd want to restrict by comment_approved,
 or order a list by date.  In that case, it's not clear how much a single
 column index like that will help.

 A query I would like to be able to do is: find the most recent comment by
 a given user/email.

 {{{
 SELECT * FROM wp_comments
     WHERE user_id = 1 AND comment_approved = 1
     ORDER BY comment_date_gmt DESC LIMIT 1
 }}}

 In that case, the best index would be the following.

 {{{
 user_id, comment_approved, comment_date_gmt
 }}}

 That index (and a similar one for comment_author_email) would also work
 for your COUNT queries (at least, the queries I'm imagining you want):

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

 SELECT COUNT( * ) FROM wp_comments
     WHERE comment_author_email = 'spammer at example.com'
     AND comment_approved = 'spam'
 }}}

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