MySql全文搜索短词不正确 [英] MySql Fulltext Search not correct with short words

问题描述

经过多次尝试和多次搜索后，我得出以下查询:

After many tries and many searches i came to the following query:

SELECT id,
       title,
       description,
       MATCH(title,description,tags) AGAINST ('$search' IN NATURAL LANGUAGE MODE) AS score
FROM pages

我使用此查询在包含音乐课程的大量页面中进行搜索.它工作得很好.页面根据它们与查询的匹配程度获得分数.

I use this query to search inside a large amounts of pages which contain music lessons. It works quite wel. Pages get a score based on how good they match to the query.

除非用户搜索诸如C Chord"或Am Chord"之类的内容，否则第一部分将被忽略.如果我搜索A 和弦"或E 和弦"，则名为C 和弦"的页面始终位于顶部.

Except when the users search for something like "C Chord" or "Am Chord" the first part is ignored. If i search for "A chord" or "E chord" the page called "C chord" is always on top.

所以..我的问题的第 1 部分是:我该如何解决?

So.. Part 1 of my question is: how can i fix that?

然后是第 2 部分.是否可以将标题"列对分数的重要性高于描述"?

Then part 2. Is it possible to give the column "Title" a bigger importance for the score then "Description"?

推荐答案

1. 问题

这已经在 SO 上讨论过很多次了:MySQL 的内置全文解析器是为 搜索单词，不是单个字符，默认最小单词长度设置为 3 (innodb) 或 4 (myisam) 这些设置意味着短于 3 或 4 个词的词不会被编入索引，因此不会被全文搜索找到.您可以将最小字符长度限制降低到 1 并重新构建索引，但它会减慢搜索速度，因为索引会更大.

This has been discussed on SO quite a few times: MySQL's built-in fulltext parser is designed for searching for words, not for single characters and comes with default minimum word length setting of 3 (innodb) or 4 (myisam) These settings mean that no words shorter than 3 or 4 words get indexed and therefore will not be found by a fulltext search. You may lower the minimum character length limit to 1 and rebuild the index, but it will slow the searching down, since the indexes will be bigger.

1. 问题

这是可能的，但您需要单独搜索 title 字段并从 title 字段提高相关性分数结果.

It is possible, but you need to search on the title field separately and bump up the relevancy score results from the title field.

您可以使用 union 获得组合列表，并使用 sum() 来总结任何记录的分数:

You can use union to get a combined list with sum() to sum the score up for any record:

SELECT p.id, any_value(title), any_value(description), any_value(tags), sum(t.score) as sum_score
FROM
    (SELECT id, (MATCH(title) AGAINST ('$search' IN NATURAL LANGUAGE MODE)) *2 AS score
     FROM pages
     UNION ALL
     SELECT id, MATCH(description,tags) AGAINST ('$search' IN NATURAL LANGUAGE MODE) AS score
     FROM pages) t
INNER JOIN pages p on t.id=p.id
GROUP BY p.id
ORDER BY sum(t.score) DESC

您需要调整全文索引才能进行单独搜索.

You need to adjust the fulltext indexes to be able to do the separate searches.

这篇关于MySql全文搜索短词不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！