From: chas
Date: April 23 1999 7:56am
Subject: Re: intersection in SQL with only one table ?
List-Archive: http://lists.mysql.com/mysql/2277
Message-Id: <3.0.5.32.19990423165628.009be7c0@mail.skinnyhippo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

Alex, you're a star !

>I've had good results using this scheme:
>
>documents 
>	docid
>	url
>	title
>
>search
>	docid
>	word
>
>then to get a list of matchings docs:
>
>	SELECT  COUNT(*) as hits, d.docid, d.url, d.title
>	FROM   search as s, documents as d
>	WHERE  (s.word = 'word1' OR
>		s.word = 'word2' OR
>		s.word = 'word3') AND
>	        s.docid = d.docid
>	GROUP BY s.docid
>	HAVING hits >= 3
>
>will do an AND match (all three terms must be there). To do an OR match
>(any terms will match), just remove the HAVING BY line. You can return
>results with best matches first (most words matching) by adding in
>'ORDER BY hits DESC' to the query. I also like the scheme because you
>can add a 'score' field to the search table and give ceartain words a
>better score, you can then pull out the score of the match using
>SUM(score).

Not wishing to be crude here, but the only words that come
to mind are "F***** A" ... you just made my day  :-)
I'd been going about this all wrong.

>My search table currently has 2.5 million words, and there is no
>noticeable delay for the user searching (through a web page). 

Cool - reassuring to know.

>Hope this helps,

This is a massive understatement....  you've saved me at least
a couple of long nites trying to patch together my scoring mechanism
that was all in script. One lesson I've really learned from this 
thread is that I've really been under-utilising SQL.

Thanks for sharing the tips - hope you have a great weekend. 
I will now, thanks to you :-)

chas