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
