Posted by Andy Hassall on 06/15/05 02:51

On Tue, 14 Jun 2005 23:47:47 GMT, "Brian" <not@given.com> wrote:

>"Andy Hassall" <andy@andyh.co.uk> wrote in message
>news:iqqua1h85agrrqkvhnhips1ffk583dfvc5@4ax.com...
>> On Tue, 14 Jun 2005 23:29:20 GMT, "Brian" <not@given.com> 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?
>>
>> Depends on your database and depends on your schema.
>
>Sorry should have said that, it's on MySQL and the schema
>is
>id INT AUTO_INCREMENT PRIMARY KEY,
> date varchar(50),
> orderno varchar(50),
> store varchar(100),
> storename varchar(100),
> dnote varchar(50),
> status varchar(20),
> product varchar(200),
> description varchar(200),
> ord varchar(50),
> del varchar(50),
> brand varchar(50),
> timestamp varchar(20)

MySQL is case-insensitive by default on strings, so the "lcase" is redundant.

You may want to add an index on the fields you're querying; an index on
storename would seem to be appropriate to support the query.

--
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[Back to original message]