Query regarding wildcards and indicies

[CHeighlund]

I've been requested to modify a program that reads from our database - the purpose of the modification is to permit the usage of wildcard searches. (Ex: find fog, foolish, and ford when searching on 'fo*')

I know I need to use the '%' character for my searches. I know I need to use LIKE. But the searches are having to parse a fairly large database, and it's taking longer than my boss would like for the search to run. Is there any way of indexing a table so as to make wildcard searches run faster, or some other way of doing this, or am I simply chasing a pipe dream?

 

[feherke]

Hi

I know I need to use the '%' character for my searches. I know I need to use LIKE.

Not exactly. Beside [tt]like[/tt], PostgreSQL can handle [tt]similar to[/tt] and POSIX matching too. And of course, through a Perl stored procedure you have access to PCRE too.

Read the Pattern Matching chapter of the PostgreSQL documentation for details.

No idea about the speed problem. I would try the mentioned alternatives, maybe one performs better.

Also I would try to rewrite the query, maybe some unwanted values could be eliminated before performing the matching.

Ex: find fog, foolish, and ford when searching on 'fo*'

Maybe would help to improve the application too, so instead of [tt]field like 'fo%'[/tt] to perform [tt]strpos(field,'fo')=1[/tt] or [tt]substr(name,1,2)='fo'[/tt].

Again, I am not sure about their speed, so try them first.


Feherke.

http://rootshell.be/~feherke/