From: | <mallah(at)trade-india(dot)com> |
---|---|
To: | <josh(at)agliodbs(dot)com> |
Cc: | <mallah(at)trade-india(dot)com>, <pgsql-sql(at)postgresql(dot)org> |
Subject: | Re: getting ILIKE or ~* to use indexes.... |
Date: | 2002-08-10 21:14:17 |
Message-ID: | 49280.203.145.129.36.1029014057.squirrel@mail.trade-india.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
Thanks Josh
I normally use tsearch for full text search i will probably use that
or may be this what u suggested.
regds
mallah.
> Rajesh,
>
>> I want my query to use indexes for company name searches but its not happening unless
>> is use '=' which does not server the purpose.
>>
>> eg
>>
>> tradein_clients=# explain SELECT co_name FROM unified_data where co_name ilike '%rajesh%' ;
>
> Well, for an *anchored* case-insensitive search, you can create an index on lower(field_name)
> to use an index.
>
> CREATE INDEX idx_uni_co_name ON unifed_data(lower(co_name));
> SELECT co_name FROM unified_data where
> lower(co_name) LIKE (lower('rajesh') || '%') ;
>
> And that will use the index.
>
> However, what you are doing is an *unanchored* text search, meaning that you are searching for
> 'rajesh' anywhere in the field. No standard index can help you with that.
>
> Instead, you should look into Full Text Search tools. There's a simple one in /contrib in the
> Postgresql source, and an more robust one
> available from the OpenFTS project.
>
> -Josh Berkus
-----------------------------------------
Get your free web based email at trade-india.com.
"India's Leading B2B eMarketplace.!"
http://www.trade-india.com/
From | Date | Subject | |
---|---|---|---|
Next Message | h012 | 2002-08-11 04:22:49 | slowing down too fast - why ? |
Previous Message | Aaron Held | 2002-08-10 21:01:31 | update on a large table |