One suggestion if I may. As this field is for email address and email are not case sensitive in the first place. Wouldn't it make sense to change the case of all the email in the database to be lower and also make sure that what even you use to enter new email address in the database actually convert it to lower before entering it in there.
Of course, I do not know your setup or if you have a good reason to use Case sensitive email addresses, but it would make sense to fix the problem at the source of it and this would eliminate the process for the future and also make your system faster as you wouldn't need to convert them every time after that. That's what I would do, but that's just me and it may or may not be a good idea for your application, but it sure wouldn't be a big deal to convert all the email to lower even if the is millions of them. But would eliminate this need for lower after the fact. Just a thought. Daniel > The table has less than 200,000 records, but the table has quite a few > columns and there is an index on ind for the columns (cust,email). MySQL > 3.23.36 is running on a Sparc with 1 gig RAM. The ind table type > is MyISAM > and the cust field is an INT and the email field is a VARCHAR. > > I was wondering if something like that may be happening. > > Thanks for the comments! > > -Joe > > > -----Original Message----- > > From: Will French [mailto:[EMAIL PROTECTED]] > > Sent: Sunday, September 30, 2001 1:25 PM > > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > > Subject: RE: why would LOWER > > > > > > You didn't mention how large your table is or if the email > > and cust fields > > are part of an index. > > > > In many situations, the results you are seeing make perfect > > sense to me. If > > email is a part of an index then the LOWER function may need > > to be performed > > on each value of email in the index before the comparison can > > be performed. > > Additionally you may be negating the effect of a hashing > > algorithm. I can > > not say with certainty how MySQL does indexed lookups, but > > many db engines > > start by narrowing down a search using a hash of the searched > > for value and > > looking that up in a hash table for the index. If the > > character set you are > > using is case sensitive (and I assume it is or you wouldn't > > need the LOWER), > > then the hashing algorithm would also be case sensitive. > > > > Will French > > > > > -----Original Message----- > > > From: Joe Kaiping [mailto:[EMAIL PROTECTED]] > > > Sent: Sunday, September 30, 2001 4:14 PM > > > To: [EMAIL PROTECTED] > > > Subject: why would LOWER > > > > > > > > > > > > Hi there, > > > > > > 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. > > > > > > Thanks, > > > Joe > > > > > > SELECT t.id, t.Name, t.city > > > FROM ind t > > > WHERE t.cust=1 > > > AND t.email='[EMAIL PROTECTED]'; > > > > > > 1 row in set (0.01 sec) > > > > > > 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) > > > > > > > > > > > --------------------------------------------------------------------- > > > 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 > --------------------------------------------------------------------- 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
