Received: from ra.sai.msu.su (ra.sai.msu.su [158.250.29.2]) by hub.org (8.9.3/8.9.3) with ESMTP id QAA43394 for ; Sat, 12 Jun 1999 16:15:05 -0400 (EDT) (envelope-from oleg@sai.msu.su) Received: from ra (ra [158.250.29.2]) by ra.sai.msu.su (8.9.1/8.9.1) with SMTP id AAA00490; Sun, 13 Jun 1999 00:14:10 +0400 (MSD) Date: Sun, 13 Jun 1999 00:14:10 +0400 (MSD) From: Oleg Bartunov X-Sender: megera@ra To: Michal Samek cc: pgsql-sql Subject: Re: [SQL] Select like when searching for whole word and optimizing it In-Reply-To: <375E289E.EF906BD2@tony.cz> Message-ID: Organization: Sternberg Astronomical Institute (Moscow University) MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII I use regex '( |^)io([^A-z]|$)' to find word 'io' For example select a.msg_id,b.key_id from publications a, keywords b where a.title ~* ('( |^)'||b.name||'([^A-z]|$)') and b.key_id in ( select key_id from keywords); This match word (b.name) in most cases. Regards, Oleg On Wed, 9 Jun 1999, Michal Samek wrote: > Date: Wed, 09 Jun 1999 10:41:02 +0200 > From: Michal Samek > To: pgsql-sql > Subject: [SQL] Select like when searching for whole word and optimizing it > > In my e-shop I'm using the searching based on select like command; > something like > SELECT * FROM kat WHERE name LIKE '%some_text%'; > and when searching for the whole word: > SELECT * FROM kat WHERE name LIKE '% some_text %'; > For this is necessary to maintain the name column with one space added > before and after the name column value (to match starting and ending > words). It's working but very ugly. > > Maybe there is some better way to search for the whole word? > > And another question, is there some way to optimize the SELECT LIKE > querry? I know that when I send something like "SELECT * FROM kat WHERE > name LIKE 'blabla%';" postgresql uses the index but not when the search > string begins with '%'... My table is quickly growing and now I can feel > the speed of my web is impacted a lot. > > There is a lot of same values in the name field (for ex. 'QUEEN' about > 40 times etc.) - I could make a table with unique only name values and > point to them from the main table kat. Will the searching be more > effective then? > > Thanks for any help; I hope it's interesting not only for me :) > -- > Michal Samek, Tony distribuce s.r.o. > webmaster@tony.cz (++420659/321350) > ICQ: 38607210 > _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83