Как я могу манипулировать релевантностью полнотекстового поиска MySQL, чтобы сделать одно поле более «ценным», чем другое?

На самом деле, использование оператора case для создания пары флагов может быть лучшим решением:

select 
...
, case when keyword like '%' + @input + '%' then 1 else 0 end as keywordmatch
, case when content like '%' + @input + '%' then 1 else 0 end as contentmatch
-- or whatever check you use for the matching
from 
   ... 
   and here the rest of your usual matching query
   ... 
order by keywordmatch desc, contentmatch desc

Опять же, это только в том случае, если все совпадения ключевых слов имеют более высокий рейтинг, чем все совпадения только для контента. Я также сделал предположение, что совпадение как по ключевому слову, так и по содержанию является наивысшим рейтингом.

Создайте три полнотекстовых индекса

• а) один в столбце ключевых слов
• б) один в столбце контента
• c) по одному столбцу ключевого слова и содержания

Затем ваш запрос:

SELECT id, keyword, content,
  MATCH (keyword) AGAINST ('watermelon') AS rel1,
  MATCH (content) AGAINST ('watermelon') AS rel2
FROM table
WHERE MATCH (keyword,content) AGAINST ('watermelon')
ORDER BY (rel1*1.5)+(rel2) DESC

Дело в том, что rel1 дает вам релевантность вашего запроса только в столбце keyword (поскольку вы создали индекс только для этого столбца). rel2 делает то же самое, но для столбца content. Теперь вы можете сложить эти две оценки релевантности вместе, применив любой вес, который вам нравится.

Однако вы не используете ни один из этих двух индексов для фактического поиска. Для этого вы используете свой третий индекс, который находится в обоих столбцах.

Индекс на (ключевое слово, содержание) контролирует ваш отзыв. Ака, что возвращается.

Два отдельных индекса (один только по ключевым словам, один только по содержанию) контролируют вашу релевантность. И вы можете применить свои собственные критерии взвешивания здесь.

Обратите внимание, что вы можете использовать любое количество различных индексов (или изменять индексы и веса, которые вы используете во время запроса, возможно, на основе других факторов ... только поиск по ключевому слову, если запрос содержит стоп-слово ... уменьшите смещение веса для ключевые слова, если запрос содержит более 3 слов... и т.д.).

Каждый индекс занимает место на диске, поэтому чем больше индексов, тем больше диск. И, в свою очередь, больший объем памяти для mysql. Кроме того, вставки будут занимать больше времени, так как вам нужно обновить больше индексов.

Вы должны оценить производительность (стараясь отключить кеш запросов mysql для сравнения, иначе ваши результаты будут искажены) для вашей ситуации. Это неэффективно для Google, но это довольно просто и «нестандартно», и это почти наверняка намного лучше, чем использование вами «нравится» в запросах.

Я считаю, что это работает очень хорошо.

Упрощенная версия, использующая только 2 полнотекстовых индекса (кредиты взяты у @mintywalker):

SELECT id, 
   MATCH (`content_ft`) AGAINST ('keyword*' IN BOOLEAN MODE) AS relevance1,  
   MATCH (`title_ft`) AGAINST ('keyword*' IN BOOLEAN MODE) AS relevance2
FROM search_table
HAVING (relevance1 + relevance2) > 0
ORDER BY (relevance1 * 1.5) + (relevance2) DESC
LIMIT 0, 1000;

Это будет искать оба полных индексированных столбца по keyword и выбирать совпадающую релевантность в два отдельных столбца. Мы исключим несоответствующие элементы (релевантность1 и релевантность2 равны нулю) и переупорядочим результаты по увеличению веса столбца content_ft. Нам не нужен составной полнотекстовый индекс.

Я сделал это несколько лет назад, но без полнотекстового индекса. У меня нет кода под рукой (бывший работодатель), но я хорошо помню технику.

Короче говоря, я выбрал «вес» из каждого столбца. Например:

select table.id, keyword_relevance + content_relevance as relevance from table
   left join
      (select id, 1 as keyword_relevance from table_name where keyword match) a
   on table.id = a.id
   left join
      (select id, 0.75 as content_relevance from table_name where content match) b
   on table.id = b.id

Пожалуйста, простите здесь любой дрянной SQL, прошло несколько лет с тех пор, как мне нужно было написать что-либо, и я делаю это с головы до ног...

Надеюсь это поможет!

J.Js

В логическом режиме MySQL поддерживает операторы «>» и «‹» для изменения вклада слова в значение релевантности, присвоенное строке.

Интересно, сработает ли что-то подобное?

SELECT *, 
MATCH (Keywords) AGAINST ('>watermelon' IN BOOLEAN MODE) AS relStrong, 
MATCH (Title,Keywords,Content) AGAINST ('<watermelon' IN BOOLEAN MODE) AS relWeak 
FROM about_data  
WHERE MATCH(Title, Keywords, Content) AGAINST ('watermelon' IN BOOLEAN MODE) 
ORDER by (relStrong+relWeak) desc

Насколько я знаю, это не поддерживается полнотекстовым поиском MySQL, но вы можете добиться эффекта, каким-то образом повторив это слово несколько раз в поле ключевого слова. Вместо того, чтобы иметь ключевые слова «foo bar», используйте «foo bar foo bar foo bar», таким образом, и foo, и bar одинаково важны в столбце ключевых слов, и поскольку они появляются несколько раз, они становятся более релевантными для mysql.

Мы используем это на нашем сайте, и это работает.

Ну, это зависит от того, что именно вы имеете в виду под:

Я хочу, чтобы строка с foo в ключевых словах имела большее значение, чем строка с foo в содержании.

Если вы имеете в виду, что строка с foo в ключевых словах должна стоять перед любой строкой с foo в содержимом, тогда я сделаю два отдельных запроса, один для ключевых слов, а затем (возможно лениво, только если его просят) другой по содержанию.

Мне нужно было что-то подобное, и я использовал решение OP, но я заметил, что полный текст не соответствует частичным словам. Таким образом, если «арбуз» находится в ключевых словах или содержании как часть слова (например, «менеджер по продажам арбузов»), он НЕ СООТВЕТСТВУЕТ и не включается в результаты из-за ГДЕ СООТВЕТСТВУЕТ. Поэтому я немного подурачился и изменил запрос ОП на это:

SELECT *, 
CASE WHEN Keywords LIKE '%watermelon%' THEN 1 ELSE 0 END AS keywordmatch, 
CASE WHEN Content LIKE '%watermelon%' THEN 1 ELSE 0 END AS contentmatch,
MATCH (Title, Keywords, Content) AGAINST ('watermelon') AS relevance 
FROM about_data  
WHERE (Keywords LIKE '%watermelon%' OR 
  Title LIKE '%watermelon%' OR 
  MATCH(Title, Keywords, Content) AGAINST ('watermelon' IN BOOLEAN MODE)) 
HAVING (keywordmatch > 0 OR contentmatch > 0 OR relevance > 0)  
ORDER BY keywordmatch DESC, contentmatch DESC, relevance DESC

Надеюсь это поможет.

Если метрика состоит только в том, что все совпадения ключевых слов более «ценны», чем все совпадения содержимого, вы можете просто использовать объединение с количеством строк. Что-то в этом роде.

select *
from (
   select row_number() over(order by blahblah) as row, t.*
   from thetable t
   where keyword match

   union

   select row_number() over(order by blahblah) + @@rowcount + 1 as row, t.*
   from thetable t
   where content match
)
order by row

Для чего-то более сложного, чем это, когда вы хотите применить фактический вес к каждой строке, я не знаю, как помочь.