List:General Discussion« Previous MessageNext Message »
From:chas Date:April 23 1999 7:56am
Subject:Re: intersection in SQL with only one table ?
View as plain text  
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

Thread
phpMichael Farr22 Apr
  • Re: phpSasha Pachev22 Apr
  • Re: phpDavor Cengija22 Apr
    • intersection in SQL with only one table ?chas22 Apr
      • RE: intersection in SQL with only one table ?Don Read22 Apr
        • RE: intersection in SQL with only one table ?chas22 Apr
          • RE: intersection in SQL with only one table ?Don Read22 Apr
      • Re: intersection in SQL with only one table ?Robin Bowes22 Apr
        • Re: intersection in SQL with only one table ?Alex Krohn22 Apr
          • Re: intersection in SQL with only one table ?chas23 Apr
  • Re: intersection in SQL with only one table ?Robin Bowes22 Apr
Re: phpPhilip Diller22 Apr