pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Select like when searching for whole word and optimizing it
4+ messages / 4 participants
[nested] [flat]

* Select like when searching for whole word and optimizing it
@ 1999-06-09 08:41  Michal Samek <webmaster@tony.cz>
  0 siblings, 3 replies; 4+ messages in thread

From: Michal Samek @ 1999-06-09 08:41 UTC (permalink / raw)
  To: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] Select like when searching for whole word and optimizing it
@ 1999-06-09 14:43  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Michal Samek <webmaster@tony.cz>
  2 siblings, 0 replies; 4+ messages in thread

From: Tom Lane @ 1999-06-09 14:43 UTC (permalink / raw)
  To: Michal Samek <webmaster@tony.cz>; +Cc: pgsql-sql

Michal Samek <webmaster@tony.cz> writes:
> Maybe there is some better way to search for the whole word? 

I think you want to build a secondary table and search that.
Take a look at the stuff in contrib/fulltextindex; it may solve your
problem as-is, and if not you can probably adapt it to your needs...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] Select like when searching for whole word and optimizing it -- regexp?
@ 1999-06-10 11:41  Emils Klotins <emils@mail.usis.bkc.lv>
  parent: Michal Samek <webmaster@tony.cz>
  2 siblings, 0 replies; 4+ messages in thread

From: Emils Klotins @ 1999-06-10 11:41 UTC (permalink / raw)
  To: Michal Samek <webmaster@tony.cz>; +Cc: pgsql-sql

[9 Jun 99,, 10:41] Michal Samek wrote:

> 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? 
Try regexp searching. Don't know if available at 6.4, but definitely is there at 6.5
SELECT * FROM kat WHERE name ~* ' your regexp ';




Emils Klotins       e-mail: emils@mail.usis.bkc.lv
Systems Manager     URL: http://www.usis.bkc.lv/
USIS Riga           7 Smilsu Str., Riga LV1050, LATVIA




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: [SQL] Select like when searching for whole word and optimizing it
@ 1999-06-12 20:14  Oleg Bartunov <oleg@sai.msu.su>
  parent: Michal Samek <webmaster@tony.cz>
  2 siblings, 0 replies; 4+ messages in thread

From: Oleg Bartunov @ 1999-06-12 20:14 UTC (permalink / raw)
  To: Michal Samek <webmaster@tony.cz>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~1999-06-12 20:14 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-06-09 08:41 Select like when searching for whole word and optimizing it Michal Samek <webmaster@tony.cz>
1999-06-09 14:43 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-06-10 11:41 ` Re: [SQL] Select like when searching for whole word and optimizing it -- regexp? Emils Klotins <emils@mail.usis.bkc.lv>
1999-06-12 20:14 ` Oleg Bartunov <oleg@sai.msu.su>

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