[wp-trac] Re: [WordPress Trac] #4366: Comment table could use some more indices

[WordPress Trac]

#4366: Comment table could use some more indices
----------------------------+-----------------------------------------------
 Reporter:  markjaquith     |        Owner:  anonymous
     Type:  enhancement     |       Status:  new      
 Priority:  normal          |    Milestone:  2.9      
Component:  Administration  |      Version:           
 Severity:  normal          |   Resolution:           
 Keywords:  needs-patch     |  
----------------------------+-----------------------------------------------
Changes (by mrmist):

  * keywords:  => needs-patch

Comment:

 If this is still desired, then as the query stands -

 SELECT comment_date_gmt FROM wp_comments WHERE comment_author_IP =
 '10.1.1.1' OR comment_author_email = 'test' ORDER BY comment_date DESC
 LIMIT 1;

 You'd need an index

 create index comment_auth ON wp_comments (comment_author_ip,
 comment_author_email, comment_date_gmt, comment_date)

 Anything less and mysql won't bother with it (though you may see different
 results depending on your test data I suppose).

 However, I'm not sure why the query orders on comment_date and fetches
 comment_date_gmt? (That seems bugish).  Unless there is some specific
 reason, I'd suggest changing the original query so that it orders on
 comment_date_gmt, as then you could make the covering index for this query
 smaller -

 create index comment_auth ON wp_comments (comment_author_ip,
 comment_author_email, comment_date_gmt)

 Sorry I have no idea how you guys write the patches that deal with
 changing the database.

-- 
Ticket URL: <http://trac.wordpress.org/ticket/4366#comment:4>
WordPress Trac <http://trac.wordpress.org/>
WordPress blogging software