Thanks to all for your responses. I had missed this important piece of information in the documentation that Benjamin pointed to and:
http://www.mysql.com/doc/C/H/CHAR.html "Values in CHAR and VARCHAR columns are sorted and compared in case-insensitive fashion, unless the BINARY attribute was specified when the table was created. The BINARY attribute means that column values are sorted and compared in case-sensitive fashion according to the ASCII order of the machine where the MySQL server is running. BINARY doesn't affect how the column is stored or retrieved." -Joe > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Benjamin > Pflugmann > Sent: Sunday, September 30, 2001 1:55 PM > To: Joe Kaiping > Cc: [EMAIL PROTECTED] > Subject: Re: why would LOWER > > > Hi. > > The problem is that LOWER(email) is an expression and expressions on > the left hand side of an comparison cannot use an index with MySQL > (see also http://www.mysql.com/doc/M/y/MySQL_indexes.html). > > If you usually want to compare emails ignoring case, an easier way is > to assure that email is not of type BINARY, i.e. "VARCHAR(x)" instead > of "VARCHAR(x) BINARY". Without BINARY, the comparisons are case > insensitive anyhow, so no need for LOWER(). > > Bye, > > Benjamin. > > > On Sun, Sep 30, 2001 at 01:36:50PM -0700, [EMAIL PROTECTED] wrote: > [...] > > > > > Is it expected that using the LOWER function should greatly > > > > increase query time? Or is there a MySQL setting that can help > > > > speed it up? Below are the results of a query against a table > > > > that has an index on cols (email,cust). Using LOWER increases > > > > the query time by 10.5 seconds. (eek!) > > > > > I've fixed the scripts that input data into the ind table so > > > > that emails are now first converted to lower case before being > > > > inserted, but was curious if doing some data mining to fix the > > > > existing emails is the only (probably best) solution. > [...] > > > > SELECT t.id, t.Name, t.city > > > > FROM ind t > > > > WHERE t.cust=1 > > > > AND LOWER(t.email)='[EMAIL PROTECTED]'; > > > > > > > > 1 row in set (10.52 sec) > [...] > > -- > [EMAIL PROTECTED] > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
