mysql - 具有相关性的全文搜索 - 为什么需要多列索引？

Question

我必须在多个列中实现全文搜索，并根据某些列/字段的相关性对结果进行加权。

我遇到的所有解决方案似乎都使​​用单列索引来计算相关性，并为 WHERE 子句使用一个多列索引。请参阅:https://stackoverflow.com/a/600915/168719或https://stackoverflow.com/a/6305108/168719

这是我的查询:

SELECT MATCH(name) AGAINST (text) as relevance_name, 
MATCH(description) AGAINST(text) as relevance_description, 
MATCH(description_long) AGAINST (text) as relevance_description_long 
FROM products WHERE

<小时/>

我面临着以下选择:

a)

MATCH(name, description, description_long) AGAINST (text) > 0

b)

MATCH(name) AGAINST (text) > 0 
OR MATCH(description) AGAINST (text) > 0 
OR MATCH(description_long) AGAINST (text) > 0

<小时/>

之后是排序子句。

ORDER BY (relevance_name * 2 + 
relevance_description * 3 + 
relevance_description_long * 4) / 9

问题是 - a(显然是首选方法)相对于 b 的优越性是什么？

a 需要创建另一个全文索引(跨所有可搜索列)，这显然会占用更多磁盘空间。

有什么优点？是性能问题吗？或者搜索质量？

Answer 1

手册第 12.9.1. Natural Language Full-Text Searches 页告诉我们:

For each row in the table, MATCH() returns a relevance value; that is, a similarity measure between the search string and the text in that row in the columns named in the MATCH() list.

因此，MATCH() 将为 MATCH (c1,c2,c3) 和 MATCH(c1) + MATCH(c2) + MATCH(c3) 返回不同的值。使用 OR 运算符进行匹配时也会出现类似的差异。

Relevance is computed based on the number of words in the row, the number of unique words in that row, the total number of words in the collection, and the number of documents (rows) that contain a particular word.

您应该使用方法B，因为它与您的查询的形式相同。