From: | will trillich <will(at)serensoft(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Performance in searchig data using "like" |
Date: | 2001-05-16 05:05:39 |
Message-ID: | 20010516000539.L29095@serensoft.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Tue, May 15, 2001 at 11:29:33PM -0500, Jorge Sarmiento wrote:
> I have a 2.5 million rows database, and when I do a search using "like" it
> tooks about a minute to give me a result.
>
> What can I do to speed up the answer? reestructurating it is not an option
> due the kind of data we have... more ram will help? using any kind of index?
>
> every suggestion will be welcome...
well, here's one: avoid the leading percent--
...where fld like 'this%'
-- CAN use index, since it's looking for strings BEGINNING with "this"
...where fld like '%that%'
-- CANNOT use index here, since it'll match "with that"
-- and "another that works" and "zoo creatures that sing"
and then, once you get your "like" searches to not use leading
%percent% marks, you may run into data dispersion trouble:
from what i've gleaned from this list, if you have 2 million
instances of "bubba" then there's not much gain to use an index
when you're searching for "bubba". but if you only have 2
hundred "gonzo" then it'll use the index when you ask to find
them.
--
What do I need manners for? I already got me a wife.
-- Adam Pontipee, "Seven Brides for Seven Brothers"
will(at)serensoft(dot)com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
From | Date | Subject | |
---|---|---|---|
Next Message | Justin Clift | 2001-05-16 05:08:55 | Re: Performance in searchig data using "like" |
Previous Message | will trillich | 2001-05-16 04:59:21 | Re: Re: naming convention/mixed case |