我如何操纵MySQL全文搜索相关性,使一个字段比另一个字段更“有价值”?

创建三个全文索引

• a) 一个在关键字列上

然后,您的查询:

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查询缓存以进行基准测试,否则您的结果将出现偏差)。这不是谷歌分级的效率,但它非常简单,而且“开箱即用”,而且几乎可以肯定比在查询中使用“like”要好得多。

实际上,使用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

同样,只有当所有关键字匹配的排名高于所有仅内容匹配的排名时,才会出现这种情况。我还假设关键字和内容的匹配是最高排名。

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,我已经好几年没有编写任何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”,这样一来,foo和bar在关键字列中都同等重要,而且由于它们出现了多次,因此与mysql的关系变得更加密切。

我们在我们的网站上使用它,它是有效的。

那要看你到底是什么意思了:

我想要一个关键字中有foo的行 比一场争吵更有关联性

如果您的意思是关键字中包含foo的行应该出现 之前 任何

我需要类似的东西,并使用OP的解决方案,但我注意到全文与部分单词不匹配。因此,如果“西瓜”作为单词的一部分出现在关键字或内容中(如watermelonsalesmanager),则它不匹配,并且由于WHERE匹配而不包含在结果中。 所以我做了一点傻事,将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

对于任何比这更复杂的事情,如果你想对每一行应用一个实际的权重,我不知道如何帮助你。