Thread: Performance in searchig data using "like"
Hello everybody! I have postgreslq 7.1 running on a 1 Ghz Intel Box, with 2 H.D. SCSI 10000 RPM connected in a DPT SCSI card with 40 MB cache RAM, 256 Mb RAM and a Intel Mainboard. 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... Thanks everybody for your time and support! Greetings from Lima - Peru! Jorge Luis Sarmiento System Administrator Lima's Chamber of Commerce www.e-camara.net P.D. Sorry for my bad English
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@serensoft.com
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
Hi Jorge, From your email it sounds like you guys aren't using any sort of indices at all. Using indices will definitely help a LOT, compared to no indices, depending on how your SQL queries access the database. Generally you're best off putting an index (or more where appropriate) on those fields which are sorted by. i.e. select foo from table bar where baz = 'Green'; For this very simple query, you'd create an index on the field baz. Indices are created either when you definte the table, or afterwards by using the CREATE INDEX command : http://www.postgresql.org/idocs/index.php?sql-createindex.html Hope that's useful. :-) Regards and best wishes, Justin Clift Jorge Sarmiento wrote: > > Hello everybody! > > I have postgreslq 7.1 running on a 1 Ghz Intel Box, with 2 H.D. SCSI 10000 > RPM connected in a DPT SCSI card with 40 MB cache RAM, 256 Mb RAM and a Intel > Mainboard. > > 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... > > Thanks everybody for your time and support! > > Greetings from Lima - Peru! > > Jorge Luis Sarmiento > System Administrator > Lima's Chamber of Commerce > www.e-camara.net > > P.D. Sorry for my bad English > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly -- "My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there." - Indira Gandhi