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...

> > 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.

./hb