Elastic search full text vs mysql full text?

Question

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.

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.

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?

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.

What are the other pros and cons I might face using MySQL full text searches against elastic search?

Answer 1

With MySQL you will always be indexing and searching your data.

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.

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.

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.

Answer 2

The main difference ElasticSearch from MySQl-search is that ES works faster when large amounts of data through indexing.

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.

This is a main reason to use ElasticSearch in HighLoad projects.

For small amounts of data you will not feel the difference.