Re: optimal search

[almir kazazic]

i already did it that way with repeating whole thing, it is lookin agly but
it works as you sad, it is especialy agly if you use more than one parametar
but how can I test it with Banchmark ?

ps i havent order it with ind i grouped it one can see this qury at
http://212.17.72.235/pressemeldung/suche.php3

Martin Ramsch <m.ramsch@stripped> wrote in message
news:<19991004020208.G21101@ style="color:#666">stripped>...
> 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 ...
>
> Regards,
>   Martin
> --
> Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/
>
> PGP KeyID=0xE8EF4F75 FiPr=5244 5EF3 B0B1 3826  E4EC 8058 7B31 3AD7
>
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail mysql-thread14994@stripped
>
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail mysql-unsubscribe@stripped instead.
>