> I have an image database with keywords assigned to each record
> in the field "keywords" and often do:
>
> select * from images where keywords like 'dog';
>
> which works great with a small database, but slows down quickly
> with a large database.
>
> since the "keywords" field can have multiple entries, such as
> 'dog mammals fuzzy small' I was considering creating a separate
> keywords table where each word was an individual row and could
> therefore be indexed and fast.
>
> however, in the above example, I would like to match on "mammal"
> also (i.e., no "s" on the end) or any substring within the field, so
> the separate table does not do it either.
>
> This has to be a common thing. Are there any good solutions or
> research out there to solve this problem? Thanks.
See: pgsql/contrib/fulltextindex
Hope this helps,
-DEJ