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应用中实现搜索功能。由于数据安全问题，我的主管不喜欢Angolia。除此之外，一个好的选择是实现弹性搜索，另一个是使用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或更高的要求。除此之外，我只能在char和text类型字段上实现全文索引。我不确定弹性搜索允许什么类型的字段。
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?
是否有合适的实现，或者我必须使用DB facade来手动执行?如果没有一个有说服力的实现，是否有具有此功能的第三方包?
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全文搜索和弹性搜索时可能会遇到的其他优缺点是什么?
3 个解决方案
#1
2
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.
有了弹性搜索，你可以更灵活地把指数作为一个单位。您可以将一个项目的所有内容注释和标记作为一个项目放在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.
您可能还会发现，一般来说，ES会比mysql提供更好的性能和更好的结果。在同义词和权重方面，您还有更多的灵活性。
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文本搜索开始，就像在一个简单的情况下，它将很快建立，如果这没有提供您需要的，然后升级到弹性搜索。您将至少有一个稻草人特性，可以用来进一步细化您的搜索需求。
#2
2
The main difference ElasticSearch from MySQl-search is that ES works faster when large amounts of data through indexing.
mysql搜索的主要区别在于，当大量数据通过索引时，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.
这是在高负荷项目中使用弹性搜索的一个主要原因。
For small amounts of data you will not feel the difference.
对于少量的数据，你感觉不到差别。
#3
-1
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，就有了TF-IDF和一个API (_termvectors)。据我所知，MySQL没有这个特性，但是可以通过多次查询数据库来实现。
#1
2
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.
有了弹性搜索，你可以更灵活地把指数作为一个单位。您可以将一个项目的所有内容注释和标记作为一个项目放在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.
您可能还会发现，一般来说，ES会比mysql提供更好的性能和更好的结果。在同义词和权重方面，您还有更多的灵活性。
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文本搜索开始，就像在一个简单的情况下，它将很快建立，如果这没有提供您需要的，然后升级到弹性搜索。您将至少有一个稻草人特性，可以用来进一步细化您的搜索需求。
#2
2
The main difference ElasticSearch from MySQl-search is that ES works faster when large amounts of data through indexing.
mysql搜索的主要区别在于，当大量数据通过索引时，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.
这是在高负荷项目中使用弹性搜索的一个主要原因。
For small amounts of data you will not feel the difference.
对于少量的数据，你感觉不到差别。
#3
-1
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，就有了TF-IDF和一个API (_termvectors)。据我所知，MySQL没有这个特性，但是可以通过多次查询数据库来实现。