Re: searching and sorting

Michael Sims wrote:

Richard Lynch wrote:

Brian A. Anderson wrote:

[...]

I am thinking of incrementally adding the resultant hits into two associative arrays with the link to the data and a calculated relevance value, and sorting this array by these relevences.

[...]

One Axiom: Keep as much of the scoring/sorting in your SQL as possible -- That's what SQL engines are best at.

I suggest the original poster look into some of the full text indexing capabilities of his SQL server. A lot of these (for PostgreSQL and MySQL anyway) will automatically return a relevance value and handle sorting based on that. As you said, that'll be a heck of a lot more efficient and easier to implement that doing it in PHP. Agreed. I use MySQL's full-text indexing and sort by the relevance score, and it works well.

If you do a search for a business at this (now retired) site: http://lwcc.gulfbridge.com , you'll see the relevance scores in parentheses next to each result. One trick I use to limit the display to the most relevant items is to look for a significant drop in this score from one item to the next. I stop listing when the score drops by 40% or so from the last item displayed. -John