laravel 弹性搜索全文 vs mysql 全文？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41892179/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Elastic search full text vs mysql full text?
提问by Rohan
I am trying to implement search functionality in my laravel app. Angolia is not preferred by my supervisors due to data security problems. Other than that one good option is to implement elastic search and another is to use full text search from mysql. I am not sure what are the pros and cons and although I have read in a few places that elastic search should be the better alternative, I would like to understand what I am working with since I haven't worked with searching before this.
我正在尝试在我的 Laravel 应用程序中实现搜索功能。由于数据安全问题，我的主管不喜欢安哥利亚。除此之外，一个不错的选择是实现弹性搜索，另一个是使用来自 mysql 的全文搜索。我不确定利弊是什么，虽然我在一些地方读到弹性搜索应该是更好的选择，但我想了解我正在使用什么，因为我之前没有使用过搜索。
I am looking at MySQL requirements and it seems I can only implement it with 5.6 or above with InnoDb engine. And other than that I can only implement fulltext indexes on char and text type fields. I am not sure what kind of fields are permitted in Elastic search.
我正在查看 MySQL 要求，看来我只能使用 InnoDb 引擎在 5.6 或更高版本上实现它。除此之外，我只能在字符和文本类型字段上实现全文索引。我不确定弹性搜索中允许哪些类型的字段。
I know I have to something like:
我知道我必须这样做：
DB::statement('ALTER TABLE posts ADD FULLTEXT search(title, body)');
Then I guess I do something like:
然后我想我会做类似的事情：
SELECT * FROM posts
WHERE MATCH (title, body)
AGAINST ('foo' IN NATURAL LANGUAGE MODE);
Is there Eloquent implementation for this or do I have to do this manually using the DB facade? If there isn't a Eloquent implementation, are there 3rd party packages with this functionality?
是否有 Eloquent 实现，或者我是否必须使用 DB 外观手动执行此操作？如果没有 Eloquent 实现，是否有具有此功能的 3rd 方软件包？
Other than that, I actually have to search in related tables too like comments and tags and so on. How is it possible to create indexes across tables or is it not possible? Is there anything in ES in which I can over come this if it is not possible to create indexes across tables in MySQL.
除此之外，我实际上也必须在相关表中进行搜索，例如评论和标签等。如何跨表创建索引或者不可能？如果无法在 MySQL 中跨表创建索引，那么在 ES 中有什么我可以克服的。
What are the other pros and cons I might face using MySQL full text searches against elastic search?
使用 MySQL 全文搜索与弹性搜索相比，我可能面临的其他优缺点是什么？
采纳答案by Jeremy French
With MySQL you will always be indexing and searching your data.
使用 MySQL，您将始终索引和搜索数据。
With ElasticSearch you have more flexibility in what you index as one unit. You could take all of content comments and tags for an item and put it in ES as one item.
使用 ElasticSearch，您可以更灵活地将索引作为一个单元。你可以把一个项目的所有内容评论和标签作为一个项目放在 ES 中。
You'll also likely find that ES will give better performance and better results in general that you would get with mysql. You also have more flexibility with things like synonyms and weighting.
您还可能会发现，与使用 mysql 相比，ES 会提供更好的性能和更好的结果。您还可以更灵活地处理同义词和权重等内容。
But it does mean you have another stack to maintain and you have to manage indexing and updating of content.
但这确实意味着您需要维护另一个堆栈，并且您必须管理内容的索引和更新。
So it will depend on your data size and the importance of search as a feature.
因此，这将取决于您的数据大小和搜索作为一项功能的重要性。
I would suggest that you start with MySql text search, as in a simple case it will be quick to set up and if this does not provide what you need then upgrade to elastic search. You will at least have a straw man feature which can be used to further refine your search requirements.
我建议您从 MySql 文本搜索开始，因为在一个简单的情况下，它会很快设置，如果这不能提供您需要的内容，则升级到弹性搜索。您至少会有一个稻草人功能，可用于进一步细化您的搜索需求。
回答by Paul Basenko
The main difference ElasticSearch from MySQl-search is that ES works faster when large amounts of data through indexing.
ElasticSearch 与 MySQl-search 的主要区别在于，当通过索引处理大量数据时，ES 的工作速度更快。
The index contains ready-made sets of data with which you are operating further ES-filters. So if you search with ES, you haven't to do a direct request to the database, as in MySQL.
该索引包含现成的数据集，您可以使用这些数据集进一步操作 ES 过滤器。因此，如果您使用 ES 进行搜索，则不必像在 MySQL 中那样直接向数据库发出请求。
This is a main reason to use ElasticSearch in HighLoad projects.
这是在 HighLoad 项目中使用 ElasticSearch 的一个主要原因。
For small amounts of data you will not feel the difference.
对于少量数据，您不会感觉到差异。
回答by bigdata2
With ES you get TF-IDF with one API (_termvectors). To my knowledge, MySQL does not have that feature, though it can be implemented by querying the database several times.
使用 ES，您可以通过一个 API (_termvectors) 获得 TF-IDF。据我所知，MySQL 没有这个功能，尽管它可以通过多次查询数据库来实现。
