I'm trying to decide between two options to achieve a prefix match for names (50 Millions options) with MySQL. The usage is for an autocomplete for search results. The dilemma is between building:
我正在尝试在两个选项之间做出决定，以便与MySQL实现名称（50百万选项）的前缀匹配。用途是搜索结果的自动填充功能。建筑之间的困境是：
- An index on the VARCHAR and performing a LIKE 'word%' query
- VARCHAR上的索引并执行LIKE'word％'查询
- A FTS (full text search) index and performing a MATCH 'word*' query
- FTS（全文搜索）索引并执行MATCH'word *'查询
Which is better for such a case? Should I consider additional FTS features for such an auto-suggest autocomplete of names?
对于这种情况哪个更好？我是否应该考虑额外的FTS功能，以便自动建议自动完成名称？
1 个解决方案
#1
3
FTS and prefix matching are two different things. So the answer depends on what your actual requirement is.
FTS和前缀匹配是两回事。所以答案取决于你的实际要求。
Do you need to return a list of all results that exactly match the condition column LIKE 'word%'? Specifically that the string must start with the word you're looking for.
您是否需要返回与条件列LIKE'word％'完全匹配的所有结果的列表？特别是字符串必须以您要查找的单词开头。
Full text search does matching based on relevance. It's not always going to give you things that match specific strings. It does stemming, it has stopwords, it omits results if a word is too common.
全文搜索根据相关性进行匹配。它并不总能给你匹配特定字符串的东西。它确实是词干，它有停用词，如果一个词太常见，它会省略结果。
I think in this case the best answer is "Full text search doesn't quite do what you think it does" So if you have precise requirements for matching, you should stick to the method that will work.
我认为在这种情况下，最好的答案是“全文搜索并不能完全按照您的想法进行操作”因此，如果您对匹配有精确的要求，那么您应该坚持使用可行的方法。
#1
3
FTS and prefix matching are two different things. So the answer depends on what your actual requirement is.
FTS和前缀匹配是两回事。所以答案取决于你的实际要求。
Do you need to return a list of all results that exactly match the condition column LIKE 'word%'? Specifically that the string must start with the word you're looking for.
您是否需要返回与条件列LIKE'word％'完全匹配的所有结果的列表？特别是字符串必须以您要查找的单词开头。
Full text search does matching based on relevance. It's not always going to give you things that match specific strings. It does stemming, it has stopwords, it omits results if a word is too common.
全文搜索根据相关性进行匹配。它并不总能给你匹配特定字符串的东西。它确实是词干，它有停用词，如果一个词太常见，它会省略结果。
I think in this case the best answer is "Full text search doesn't quite do what you think it does" So if you have precise requirements for matching, you should stick to the method that will work.
我认为在这种情况下，最好的答案是“全文搜索并不能完全按照您的想法进行操作”因此，如果您对匹配有精确的要求，那么您应该坚持使用可行的方法。