I am creating an online shopping website.
customers post their products on this website. product table can fields that needs to be searched, such as product title, product description, product category, product location, etc...
I want to use ElasticSearch to store product table, so users can search the products.
all of my data is stored in a MySQL database, for example, I have Users Table, Product table, Message Table, etc...
Out of all these tables, only product table need to be search and it is the only table which needs to stored in ElasticSearch.
Now, I am not sure which is the best approach for implementing this:
Is it better to store all the data including Product table in MySQL and then duplicate the Product table into ElasticSearch, so it can be searched... using for example go-mysql-elasticsearch?
Instead of duplicating the product table, can I just store the Product table in ElasticSearch? this seems simple and there is no need to sync the data...
I am not sure how reliable is option 2? If I store the data in MySQL, I know I can frequently back it up... another important question is using MySQL, I can get a backup of Production DB and restore it on Test Server... If I have a production ES server and a Test ES server, can I still do a backup and restore from prod to test?
I would appreciate any recommendation.