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

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` DESCLIMIT 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.

you can add multiple mysql MATCH() values together, first multiplying each one by 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 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 CASEWHEN `name`        LIKE "searchterm%"  THEN 20WHEN `name`        LIKE "%searchterm%" THEN 10WHEN `description` LIKE "%searchterm%" THEN 5WHEN `url`         LIKE "%searchterm%" THEN 1ELSE 0ENDLIMIT 20

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