Alex, you're a star !
>I've had good results using this scheme:
>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
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 :-)