通过使用布尔全文搜索的相关性排序
我在使用MySQL的全文搜索并按相关性顺序返回结果时遇到了问题。 我必须使用布尔全文搜索，它不会按相关性顺序返回结果。 但是，我需要这些结果的相关性顺序。 如果我试图在查询的末尾添加一个order by子句，则查询结果为使用filesort，这会使查询非常慢（比没有慢1000多倍）。 我不确定可以做什么。
这是我的查询：
SELECT g.id, MATCH(g.searchable_name) AGAINST ('test*' IN BOOLEAN MODE) AS relevance
FROM games g
WHERE MATCH(g.searchable_name) AGAINST ('test*' IN BOOLEAN MODE)
ORDER BY relevance DESC
LIMIT 0, 31
提前致谢。
起初你应该考虑IN BOOLEAN MODE不返回分数，而是返回二进制（1 = found，0 = not found）：
mysql>SELECT
topic_id,
MATCH(topic_text) AGAINST('+tuning' IN BOOLEAN MODE) AS binary
FROM
topics_search
LIMIT 10
+----------+----------+
| topic_id | binary |
+----------+----------+
| 2 | 0 |
| 4 | 0 |
| 5 | 0 |
| 6 | 1 |
| 7 | 0 |
| 8 | 0 |
| 11 | 0 |
| 12 | 0 |
| 13 | 0 |
| 14 | 0 |
+----------+----------+
10 rows in set (9 ms)
只有自然的全文搜索才能生成分数（因为它是默认模式，所以未给出IN NATURAL LANGUAGE MODE修饰符）：
mysql>SELECT SQL_NO_CACHE
topic_id,
MATCH(topic_text) AGAINST('tuning') AS score
FROM
topics_search
WHERE
host_id = 1
ORDER BY
score DESC
LIMIT 10
+--------------------+--------------------+
| topic_id | score |
+--------------------+--------------------+
| 153257 | 5.161948204040527 |
| 17925 | 4.781417369842529 |
| 66459 | 4.648380279541016 |
| 373176 | 4.570812702178955 |
| 117173 | 4.55166482925415 |
| 167016 | 4.462575912475586 |
| 183286 | 4.4519267082214355 |
| 366132 | 4.348565101623535 |
| 95502 | 4.293642520904541 |
| 29615 | 4.178250789642334 |
+--------------------+--------------------+
10 rows in set (478 ms)
附注：令人难以置信的慢，因为score不能有索引。
所以你需要自然搜索按分数排序。 但自然搜索不支持*通配符等运算符。 现在我们遇到了困境，因为它在BOOLEAN搜索tunin*并不有用，并且使用关键tunin在NATURAL进行并行搜索，因为没有文本会包含该部分词汇。
mysql>SELECT SQL_NO_CACHE
topic_id,
MATCH(topic_text) AGAINST('tunin') AS score
FROM
topics_search
WHERE
MATCH(topic_text) AGAINST('tunin*' IN BOOLEAN MODE)
AND
MATCH(topic_text) AGAINST('tunin') > 0
ORDER BY
score DESC
LIMIT 10
Empty set (170 ms)
结论
无法使用通配符运算符进行搜索并按相关性对结果进行排序。
除非您找到一种方法来获取全文索引中由通配符搜索命中的所有单词，并在第二个查询中使用它们，或者根据LIKE构建自己的分数，并计算一个结果行内的单词数量。 有趣的是打开一个新的问题。