ES result to mysql query


(Anton Vorontsov) #1

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?


(David Pilato) #2

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?


(Anton Vorontsov) #3

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


(David Pilato) #4

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.


(system) #5

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.