Alex Rivera | Logout

Best way to do a weighted search over multiple fields in mysql?

Asked 2011-06-27T17:52:57.830
26

Here's what i want to do:

  • match a search subject against multiple fields of my table
  • order the results by importance of the field and relevance of the matching (in that order)

Ex: let's assume I have a blog. Then someone searches for "php". The results would appear that way:

  • first, the matches for the field 'title', ordered by relevance
  • then, the matches for the field 'body', ordered by relevance too
  • and so on with the specified fields...

I actually did this with a class in PHP but it uses a lot of UNIONS (a lot!) and grows with the size of the search subject. So I'm worried about performance and DOS issues. Does anybody has a clue on this?

Edit
Report

1 Answer

1

I had this exact same question and it was fully answered on one of the MySQL forums. Here's the thread. Kind of a long thread (because I'm kind of long-winded) but the payoff is just what you're looking for.

answered 2011-06-27T18:10:46.457

Your Answer