Лучший способ выполнить взвешенный поиск по нескольким полям в mysql?

Вот что я хочу сделать:

  • сопоставьте тему поиска с несколькими полями моей таблицы
  • упорядочить результаты по важности поля и значимости соответствия (в таком порядке)

Пример: предположим, у меня есть блог. Затем кто-то ищет "php". Результаты будут выглядеть следующим образом:

  • во-первых, совпадения для поля "название", упорядоченные по релевантности
  • затем совпадения для поля "тело", упорядоченные по релевантности тоже
  • и так далее с указанными полями...

На самом деле я сделал это с классом на PHP, но он использует много СОЮЗОВ (много!) и растет с размером темы поиска. Поэтому меня беспокоят проблемы с производительностью и DOS. У кого-нибудь есть ключ к разгадке этого?

Author: hugo_leonardo, 2011-06-27
Source

5 answers

Вероятно, этот подход к выполнению взвешенного поиска/результатов подходит для вас: 

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

Он использует подзапрос select, чтобы указать вес для упорядочения результатов. В этом случае выполняется поиск по трем полям, вы можете указать вес для каждого поля. Это, вероятно, дешевле, чем профсоюзы, и, вероятно, один из самых быстрых способов только в обычном MySQL.

Если у вас больше данных и вам нужны более быстрые результаты, вы можете рассмотреть возможность использования чего-то вроде Sphinx или Lucene.

 33
Author: hakre, 2012-09-21 09:16:38

Вы можете добавить несколько значений mysql MATCH() вместе, сначала умножив каждое из них на их вес.

Упрощенный, конечно...

'(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'

Затем

'ORDER BY relevance'
 9
Author: dqhendricks, 2014-11-10 22:33:57

Вам следует использовать специальный индексатор для предварительной выборки всех данных в оптимизированный индекс с возможностью поиска. Сфинкс и аналогичные продукты делают это очень хорошо.

 1
Author: George Cummins, 2011-06-27 18:00:50

У меня был точно такой же вопрос, и на него был дан полный ответ на одном из форумов MySQL. Вот нить. Довольно длинная нить (потому что я немного многословен), но выигрыш - это именно то, что вы ищете.

 1
Author: Pete Wilson, 2011-06-27 18:10:46

Существует собственный и простой способ сделать это с помощью функции CASE MySQL (https://dev.mysql.com/doc/refman/5.7/en/case.html ).

Пример (непроверенный): 

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

Я использовал это для многих собственных взвешенных поисков и получаю абсолютное удовольствие!

 0
Author: Harvey Dobson, 2018-07-18 16:03:56