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
