Re: LIKE search and performance

[Guido Neitzer <lists(at)event-s(dot)net>]

Am 23.05.2007 um 09:08 schrieb Andy:

> I have a table with varchar and text columns, and I have to search  
> through these text in the whole table.
>
> An example would be:
> SELECT * FROM table
>                              WHERE name like '%john%' or street  
> like '%srt%'
>
> Anyway, the query planner always does seq scan on the whole table  
> and that takes some time. How can this be optimized or made in  
> another way to be faster?

The problem is that normal indexes cannot be used for "contains"  
queries.

If you need fulltext search capabilities you have to take a look at  
tsearch2 or an external search engine like Lucene.

cug