Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

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?

like image 820
Hugo Mota Avatar asked Jun 27 '11 17:06

Hugo Mota


1 Answers

Probably this approach of doing a weighted search / results is suitable for you:

SELECT *,     IF(             `name` LIKE "searchterm%",  20,           IF(`name` LIKE "%searchterm%", 10, 0)       )       + IF(`description` LIKE "%searchterm%", 5,  0)       + IF(`url`         LIKE "%searchterm%", 1,  0)     AS `weight` FROM `myTable` WHERE (     `name` LIKE "%searchterm%"      OR `description` LIKE "%searchterm%"     OR `url`         LIKE "%searchterm%" ) ORDER BY `weight` DESC LIMIT 20 

It uses a select subquery to provide the weight for ordering the results. In this case three fields searched over, you can specify a weight per field. It's probably less expensive than unions and probably one of the faster ways in plain MySQL only.

If you've got more data and need results faster, you can consider using something like Sphinx or Lucene.

like image 86
hakre Avatar answered Oct 14 '22 05:10

hakre