|
|
Posted by Brian on 06/16/05 02:30
Thanks for your replies guy, I do have 2 questions
and this may be because I have been advised wrongly here
1) If running on a Linux system I thought that it would be case
sensitive ? if I ask it to do a search where a field was ABC123 and
I ask it to search for AbC123 would it still work?
2) this maybe because I have been miss informed on what indexing does,
but can you explain why adding an index will help and the example I
have given is only on 1 search, another is
SELECT DISTINCT orderno,store,storename,date,dnote FROM $table WHERE
lcase(store) = lcase('$store) ORDER BY dnote
so should I make an index for 'store'
thanks in advance (you never know I might end up learning stuff !)
Brian
"Chris Hope" <blackhole@electrictoolbox.com> wrote in message
news:d8nqig$ntj$1@lust.ihug.co.nz...
> 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
Navigation:
[Reply to this message]
|