List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:October 4 1999 12:02am
Subject:Re: optimal search
View as plain text  
On Sun, 1999-10-03 17:38:48 +0200, Almir Kazazic wrote:
> ok I have to search the table for some keywords and display the result in
> best way(best matching first)
> right now I am doing it this way
> select Ind, ueberschrift,
> ( 
> (if(LOCATE('key1', LOWER(ueberschrift))>0,3,0))+
> (if(LOCATE('key1', LOWER(einlitung))>0,2,0))+
> (if(LOCATE('key1', LOWER(kurzzf))>0,4,0))+
> (if(LOCATE('key1', LOWER(lauftext))>0,2,0))+
> (if(LOCATE('key2', LOWER(ueberschrift))>0,3,0))+
> (if(LOCATE('key2', LOWER(einlitung))>0,2,0))+
> (if(LOCATE('key2', LOWER(kurzzf))>0,4,0))+
> (if(LOCATE('key2', LOWER(lauftext))>0,2,0))+       
> 0) as score 
> from meldung group by ind order by score desc
> Ind is ID and the rest are text fields
> now I have two problems first of all I am getting the results that
> have score 0 because (where score>0 is not working) I may not use
> field score ?

Unfortunately we can't use the field aliases within the WHERE clause,
but you can repeat the whole term!  It looks ugly, but it works ...

... WHERE ( if(LOC...)+0) > 0 ORDER BY score DESC;

BTW, why do you ORDER BY ind?

> second I would like to do it with like '%key%' instead of locate I
> think would be faster, but I am not shore, because like is not case
> sensitive

You always can do your own tests with the BENCHMARK function! ;-)

SELECT BENCHMARK( 1000000, "LOCATE('key1', LOWER(lauftext))" )
FROM meldung;

SELECT BENCHMARK( 1000000, "lauftext LIKE '%key1%'" )
FROM meldung;

SELECT BENCHMARK( 1000000, "LOWER(lauftext) REGEXP 'key1'" )
FROM meldung;

and see how long these queries run ...

Martin Ramsch <m.ramsch@stripped> <URL: >
PGP KeyID=0xE8EF4F75 FiPr=5244 5EF3 B0B1 3826  E4EC 8058 7B31 3AD7
optimal searchalmir kazazic3 Oct
  • Re: optimal searchMartin Ramsch4 Oct
Re: optimal searchalmir kazazic4 Oct