Best option for working with MySQL

[ChristopherHS (Christopher)]

Hello,
We currently use Percona but to improve the search, we want to delegate this task to Elasticsearch.
We have a videos table which contains over 4 million recordings.

The search is done on all the fields below linked to the videos table

videos	producers	actors	categories	tags
title	name	names	names	names
+12 translations	-	-	+12 traductions	+12 traductions

We want to know from your experience, what is the best solution to recover the result:

1. Save only the fields that will be used for research on Elasticsearch, do a search by retrieving the IDs then do a second query on the main database to retrieve the information to display to users

2. Save on Elasticsearch all the fields fields that will be used for research and display, make a single request to retrieve all the information

Thank you for your help and advices.

[warkolm (Mark Walkom)]

I would say 2 as it's easier and probably quicker. Yes, there is a cost in storing the data twice, but there is a cost in the search and then DB call in your first option.

At the end of the day, you need to figure out which cost is the one you are ok with incurring based on your needs and budget.

[ChristopherHS (Christopher)]

Hi @warkolm ,

Thank you for your reply.

I will do as you advise, we think the same thing.