--- In firebird-support@yahoogroups.com, "andrew_s_vaz"
<andrew_s_vaz@b...> wrote:

>
> Adam,
>
>
> > I believe (and correct me if I am wrong) that the locate function on
> >a query component is processed by the application. That means that
> >the database will have to return all the records to the application,
> >and the component can then do a search for it. Even if the component
> >does use some form of binary search on the dataset, you have already
> >paid a performance penalty, especially if the database is on a
> >different machine and you have network speed to factor in.
>
> Narf. That would be plain horrible. If that's true, I'll have a loong
> time changing all the searches on my app to do selects insead of

locates.

>
> hmm.. suddenly I got that "OMGIF" feeling about my app and me.
>
> >
> > The like operator will automatically try and use the available index.
> >
> > select name
> > from employee
> > where name like 'A%'
> >
> > will use the index on 'name', but
> >
> > select name
> > from employee
> > where name like '%a'
> >
> > can not use the index, and so doesn't.
>
> Any known way so the <like '%a'> can use an index? Or simply it just
> can't use it? My users like to do wierd searches and I've already seen
> them doing this kind of search. :-)
>
> >
> > Hope that helps
>
>
> Thanks a lot.
>
> Andrew

Imagine you have 10 names, and your ascending index looked a bit like
this (OK, it doesn't look like this, but lets not get technical, I am
answering a why question):

Adrian
Anthony
Aarom
Ben
Bob
Cameron
James
Jenny
Karen
Martin

The following query would be easy

select name
from employee
where name like 'J%'

FB would know by the index that it only needed to return James and Jenny.

But how could that index help if your query went something like

select name
from employee
where name like '%i'

By inspecting 1 by 1, we know it is only Adrian and Martin, but the
index can't help on this.

You could possibly create a table called pattern with an index on it,
and maintain it using triggers on the employee table, but it would
grow very big. It is a trade-off you will have to decide the best way
of handling.

Sorry I can't be of more help.