--- In firebird-support@yahoogroups.com, Helen Borrie <helebor@t...>
wrote:

> At 01:44 AM 28/11/2004 +0000, you wrote:
> >--- In firebird-support@yahoogroups.com, Helen Borrie

<helebor@t...>

> >wrote:
> > > LIKE is a dog, as it can't use an index. In v.1.5, the

optimizer will

> > > change LIKE 'anything%' to STARTING WITH, in any case. STARTING
> >WITH does
> > > use an index.
> >
> >Yes, that's what I understood about LIKE. But from what you

describe,

> >because we're using 1.5.1 (and have an index on that column), if

the

> >optimiser has replaced it with STARTING WITH, then I guess we're

stuffed.

>
> I totally miss your point here. If you WANT the optimiser to

ignore an

> index, there are other things you can do...

No no, I don't want to ignore the index.
You said that LIKE will be replaced with STARTING WITH by the
optimiser for 1.5 and above. We are using 1.5.1. We also have an index
on the column. Therefore if the optimiser has done its work, it
doesn't matter that we use LIKE because the optimiser will do the
'right' thing.
If this is the case then (other than explicitly specifying STARTING
WITH - which should be unnecesary) there is nothing more we can do;
the code should be running optimally.

I just re-tested the query:
Using LIKE on XP: 640ms; on the MAC: 2203ms
Using STARTING WITH on XP: 640ms; on the MAC: 2158ms

> > > If you are going to do like '%magic%' then use CONTAINING

instead. It

> > > doesn't use an index, but it is case-sensitive and (I think)

smarter

> >than
> > > the wildcard search. Confine LIKE to conditions where you

genuinely

> >need
> > > the search to be wildcard.
> >
> >ok, but the search (not mentioned earlier)
>
> "search" = "anything that has to be compared" - hence, a WHERE

clause

> specifies criteria for a search, as does the ON clause of a joned

query.

>
> >does need to ignore case,
> >so what is the overhead with specifying "... where upper(mycolumn)
> >containing 'MAGIC' " ?
>
> upper(mycolumn) is an expression, so there's a calculation each

time to

> uppercase the current column value *before* the search of the string

can

> begin (one more step possibly). If you are always certain that the

search

> value will be upper case, probably very little difference - both

ways are

> slow because neither can use an index. But of course, if you force

an

> upper case search, it's not case-insensitive...the difference is

that you

> can throw anything at CONTAINING and it will find matches that are

either

> upper or lower case. Pick the horse for the course.

Ok, I misunderstood you - you stated earlier that CONTAINING was
case-sensitive. Was that a typo? Conventionally when someone states
that something is case-sensitive, it means that if for example I'm
searching for 'magic' and the only similar word is 'Magic', then it
will not be found.
I want a case-insensitive search (insensitive to the case of the word
I'm searching for). From what you've just further described about
CONTAINING, it's just what I need.

So I just ran a test using CONTAINING - no significant change in
performance. On XP it takes 578ms, on the MAC 2031ms.

Slightly better performance than using LIKE/STARTING WITH, but nothing
too spectacular.

Thanks