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

>
> At 10:07 PM 2/12/2007, you wrote:
> >Hello folks,
> >
> >First of all thank you for setting up this group.
> >
> >Second, I have a question regarding indexes and the CONTAINING in a
> >SELECT. I think I have reade somewhere (not quite sure where or if I
> >read it correctly), but I want to confirm if using CONTAINING, it
> >makes use of indexes we create.
> >I know that like does not use index. But, if we, for example, use like
> >with '%text', then it gets translated to STARTS WITH 'text', which
> >uses indexes.
> >
> >I know that CONTAINING is case insensitive, and I was wondering if we
> >can use indexes with it.
>
> CONTAINING cannot use an index.
>
> >If not, than what is the alternative solution we can use?
>
> Store keywords, index those and search on the keywords? The trick

is to store each keyword once, uniquely and also case-insensitively if
you want, in a separate table. Create an intersection table with
foreign keys to the document record and the keyword record. Then your
SELECT will find the ids of documents containing the matching keyword
or STARTING WITH a stub string, both of which can make use of the indexes.

>
> ./heLen
>

Hi Helen,


Thanks for the prompt reply.

With regards to my question, I do use the alternative you have just
mentioned, but for huge amounts of text. The solution I was looking
for is more for short strings such as fullName of an individual. Then,
I may want to find all the people that have the name in the beginning,
middle or end of the string. But I guess the solution would be to
split the full name into first, middle and last, as well as have the
column that stores the combination of the three. Then, index the
first, middle, and last, and search on that. That more or less is what
you have described, right?

Thanks once again.

All the best,

Fidel.