Thread: What index for 'like (%keyword%)' ???

Hi everyone,

I am building a query which uses a clause like "Where doc_description like
'%keyword%'". I know a normal index won't be of any use here, but since the
table in question will get fairly big, I do want to use an index.

Can anyone give me some advise on what kind of index I can use here? Or
shouldn't I use one in this case?

Kind regards,
Alexander Priem.

On Wednesday 31 March 2004 10:51, Priem, Alexander wrote:
> Hi everyone,
>
> I am building a query which uses a clause like "Where doc_description like
> '%keyword%'". I know a normal index won't be of any use here, but since the
> table in question will get fairly big, I do want to use an index.
>
> Can anyone give me some advise on what kind of index I can use here? Or
> shouldn't I use one in this case?

You probably want to look at the contrib/tsearch2 full-text indexing module.

--
  Richard Huxton
  Archonet Ltd

> I am building a query which uses a clause like "Where doc_description like
> '%keyword%'". I know a normal index won't be of any use here, but since the
> table in question will get fairly big, I do want to use an index.
>
> Can anyone give me some advise on what kind of index I can use here? Or
> shouldn't I use one in this case?

You have to use a proper full text indexing scheme.  Investigate
contrib/tsearch2 module in the postgres distribution.

Chirs