[ACCEPTED]-Best way to do a weighted search over multiple fields in mysql?-search

Probably this approach of doing a weighted 9 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 8 uses a select subquery to provide the weight 7 for ordering the results. In this case three 6 fields searched over, you can specify a 5 weight per field. It's probably less expensive 4 than unions and probably one of the faster 3 ways in plain MySQL only.

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

you can add multiple mysql MATCH() values 2 together, first multiplying each one by 1 their weight.

simplified of course...

'(MATCH(column1) AGAINST(\''.$_GET['search_string'].'\') * '.$column1_weight.')
 + (MATCH(column2) AGAINST(\''.$_GET['search_string'].'\') * '.$column2_weight.')
 + (MATCH(column3) AGAINST(\''.$_GET['search_string'].'\') * '.$column3_weight.')
 AS relevance'

then

'ORDER BY relevance'

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

Example 3 (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 2 searches of my own and works an absolute 1 treat!

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 4 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 3 we have these values and search for install: (1 2 column example, but it works with multiple 1 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

You should use a dedicated indexer to prefetch 3 all of the data into an optimized, searchable 2 index. Sphinx and similar products do this very 1 well.

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