[wp-trac] [WordPress Trac] #9642: Database Schema Optimizations

[WordPress Trac]

#9642: Database Schema Optimizations
-------------------------------+-----------------------------
 Reporter:  Denis-de-Bernardy  |       Owner:
     Type:  enhancement        |      Status:  assigned
 Priority:  normal             |   Milestone:  Future Release
Component:  Optimization       |     Version:  2.8
 Severity:  normal             |  Resolution:
 Keywords:  needs-patch        |
-------------------------------+-----------------------------

Comment (by Otto42):

 Replying to [comment:22 dbuser123]:
 > Posted before but still not fixed.
 >
 > Another slow query (0.30s for a 50 MB comments table) I regularly
 encounter is this one:
 > {{{
 > SELECT comment_post_ID
 > FROM comments
 > WHERE LCASE(comment_author_email) = 'user at host.com' AND
 comment_subscribe='Y' AND comment_approved = '1'
 > GROUP BY comment_post_ID
 > }}}
 > It would be better to always store e-mail as lowercase, stop using the
 LCASE function, and add an index on (comment_author_email,
 comment_post_ID).

 Old, but it needs to be said: If we're doing this somewhere, then we're
 doing it wrong.

 Email addresses are case sensitive. The local part, anyway. See RFC 2821:
 http://www.faqs.org/rfcs/rfc2821.html

   The local-part of a mailbox
   MUST BE treated as case sensitive.  Therefore, SMTP implementations
   MUST take care to preserve the case of mailbox local-parts.  Mailbox
   domains are not case sensitive.  In particular, for some hosts the
   user "smith" is different from the user "Smith".  However, exploiting
   the case sensitivity of mailbox local-parts impedes interoperability
   and is discouraged.

 While most all systems nowadays are case insensitive, technically in the
 first part of the email address, case matters. So we shouldn't be using
 LCASE here at all, or lowercasing email addresses.

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