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

user_434293 0
Viewed 11575

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?

search mysql php
6 Answers

There is a native and clean way to do this using MySQL's CASE function (https://dev.mysql.com/doc/refman/5.7/en/case.html).

Example (untested):

SELECT * FROM `myTable` 
WHERE (`name` LIKE "%searchterm%" OR `description` LIKE %searchterm%" OR `url` LIKE "%searchterm%")
ORDER BY CASE
WHEN `name`        LIKE "searchterm%"  THEN 20
WHEN `name`        LIKE "%searchterm%" THEN 10
WHEN `description` LIKE "%searchterm%" THEN 5
WHEN `url`         LIKE "%searchterm%" THEN 1
ELSE 0
END
LIMIT 20

Have used this for many weighted searches of my own and works an absolute treat!

user_10353650 
SELECT post_name, post_title,
    (CASE WHEN `post_name` LIKE '%install%' THEN(9 / LENGTH(post_name) * 100) ELSE 0 END) 
    + (CASE WHEN `post_title` LIKE '%install%' THEN(9 / LENGTH(post_title) * 50) ELSE 0 END)
        AS priority
FROM wp_posts
WHERE
    post_title LIKE '%install%'
    OR post_name LIKE '%install%'
ORDER BY priority DESC

This query will not only check weight in columns, but also in each row:

  • Checks how important search word is in each field cell. For example install wins over install something if searching for install (length is included in weight calculation).
  • Each field can have assigned weights (100 and 50 in this case, optional).

Basically, if we have these values and search for install: (1 column example, but it works with multiple columns too)

  • "Something else about install"
  • "install"
  • "install something"

Search will gives this order:

  • "install" - 128 weight
  • "install something" - 52 weight
  • "Something else about install" - 32 weight
user_26761370
Related