CODEWITHSUNDEEP
phpmysqlsearch
Hugo Mota
Hugo Mota

Reputation: 11557

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

Here's what i want to do:

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

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?

Upvotes: 26

Views: 13363

Answers (6)

ViliusL
ViliusL

Reputation: 4715

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

Upvotes: 2

Harvey Dobson
Harvey Dobson

Reputation: 86

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!

Upvotes: 3

dqhendricks
dqhendricks

Reputation: 19251

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'

Upvotes: 10

hakre
hakre

Reputation: 197732

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.

Upvotes: 44

Pete Wilson
Pete Wilson

Reputation: 8694

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.

Upvotes: 1

George Cummins
George Cummins

Reputation: 28906

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

Upvotes: 1

Related Questions