pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Oleg Bartunov <oleg@sai.msu.su>
To: Michal Samek <webmaster@tony.cz>
Cc: pgsql-sql <pgsql-sql@postgreSQL.org>
Subject: Re: [SQL] Select like when searching for whole word and optimizing it
Date: Sun, 13 Jun 1999 00:14:10 +0400 (MSD)
Message-ID: <Pine.GSO.3.96.SK.990613000929.29126B-100000@ra> (raw)
In-Reply-To: <375E289E.EF906BD2@tony.cz>

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 <webmaster@tony.cz>
> To: pgsql-sql <pgsql-sql@postgreSQL.org>
> 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




view thread (4+ messages)

Message-ID: <Pine.GSO.3.96.SK.990613000929.29126B-100000@ra>
Permalink:  ../Pine.GSO.3.96.SK.990613000929.29126B-100000@ra/
Also on:    postgresql.org/message-id/Pine.GSO.3.96.SK.990613000929.29126B-100000@ra

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: oleg@sai.msu.su, webmaster@tony.cz
  Subject: Re: [SQL] Select like when searching for whole word and optimizing it
  In-Reply-To: <Pine.GSO.3.96.SK.990613000929.29126B-100000@ra>
  References: <375E289E.EF906BD2@tony.cz> <Pine.GSO.3.96.SK.990613000929.29126B-100000@ra>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox