ES result to mysql query

[symbios-zi (Anton Vorontsov)]

Hello there.
There is a project with MVC framework using MySQL and a products table with description field. I want to use ElasticSearch for full-text search of products.

I have made indexing products to ES and can find products in ElasticSearch. But after that I want still use mysql to get this founded products from Mysql becouse all of products has own relations.

The only one idea I found is getting all ID's of founded results in ES and making query to mysql by this ID's.

It there any another solution to I get data from Mysql using results of ElasticSearch?

[dadoonet (David Pilato)]

Let say that you have an entity 1 in MySQL.

When you index it in elasticsearch, just use this PK as the document id:

PUT index/doc/1
{
}

Then in the response object, you will get back the _id of the 10 first matching documents.
Just use this id to do a lookup in your DB.

Is that what you are looking at?

[symbios-zi (Anton Vorontsov)]

For example I made full text search and it returns about 200 items. I will get 200 ids from Elastic to array, and make a query to Mysql like

SELECT *
FROM products
WHERE id IN (1, 2, 5, 7, 8, 10 ...200);

Is it right way? I guess that it will work slow if I will be use 200 IDs in query

[dadoonet (David Pilato)]

I guess that it will work slow if I will be use 200 IDs in query

Probably but that's not an elasticsearch question, right?

If you just want to display results, then reading the elasticsearch _source field will be faster I think than doing lookups in another db.