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