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

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.

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

[Back to original message]