Posted by Chris Hope on 06/15/05 02:51

Chris Hope wrote:

> Brian wrote:
>
>> I am running the follwoing statment
>>
>> SELECT DISTINCT orderno,store,storename,date,dnote FROM $table WHERE
>> lcase(storename) = lcase('$town') ORDER BY dnote
>>
>> The trouble is it's when searching a table of 40,000 its slow, is
>> there a better way?
>
> Because you are running lcase() against a column it will have to do
> the comparison against every single record in the table, even if that
> column is indexed. Is there any reason you are doing this? As long as
> the column is not a binary type then string comparisons are case
> insensitive so there's no need to lowercase the compared strings. I
> would suggest removing the lcase() functions and indexing the
> storename column. That should see a significant speed increase.

Oh, and your index does not need to contain the full 100 characters of
that column (I saw your schema in your other post).

For example to create an index which stores only the first 10 characters
of the column you'd do this:
ALTER TABLE [tablename] ADD INDEX ( storename(10) )

--
Chris Hope | www.electrictoolbox.com | www.linuxcdmall.com

[Back to original message]