List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:August 9 1999 8:47pm
Subject:Re: Subqueries, joins & select ... IN(...) search engines
View as plain text  
David Lennartsson wrote:
> Hi!
> I'm trying to build a small searchengine for documents.
> First I tried to
> build it with subselects, but we all know now that they aren't supported.
> After that I did it as in yesterday thread ( I stored the lWordRef in an
> variablelength string and did an select lDocref from doclink where
> lWordRef IN (...) ). It works well but an simple join may be nicer, BUT
> the joins turn out to join the whole tables if I do an szWord LIKE ... OR
> szWord LIKE ... .(takes about 20 seconds, toooo loong).. the join do space
> out totally if i search on szWord LIKE "%sql" (no index).. Doing it with
> select ... ; store in string select ...IN (...) makes it in about
> 10seconds when the join took about 3,5 minutes ;)
> I can't find a way around that except using the string and make a
> new select using IN (...). It's only one or two seconds slower on ordinary
> queries and it never fails. Can we speed it up?
> But my real question is about the next step.
> I do queries like to search for the word linux and mysql. It also returns
> the number of hits in each document. See below.
> mysql> select lDocRef,count(lDocRef) as freq from doclink,words where
> doclink.lWordRef = words.lWordRef and szWord LIKE "linux" or szWord LIKE
> "mysql" GROUP BY lDocRef;
> Now I also want to want to do queries like Find: (linux or mysql) and
> (answer or question).
> Right now i'm doing two selects like above, store the results in an linked
> list. Then I make an "itemwise"-AND on the two lists. In the new linked
> list I only have documents existing in both queries resultset. Of course I
> do add the total freq to the new list's datapart.
> Can I do all of the above in one nice query with joins, subselects or
> whatever? And not that slow either, it moves quite fast already..
> Thank you all for good discussions!
>         /dle - there has to be a way to do joins with OR, or?
< cut > 

Hi David

JOINs are not always the best solution!

In your case the IN(...) method seems to be the best I can think of.

If you want to do the (linux or mysql) and (answer or question) thing with JOINs, then you
have to do a selfjoin of words under another alias.

Instead of using linked lists, you could use a temporary table within mysql.


Subqueries, joins & select ... IN(...) search enginesDavid Lennartsson6 Aug
  • Re: Subqueries, joins & select ... IN(...) search enginesChristian Mack9 Aug
    • Re: Subqueries, joins & select ... IN(...) search enginesDavid Lennartsson9 Aug
      • Re: Subqueries, joins & select ... IN(...) search enginesThimble Smith9 Aug