Using Elasticsearch to do complex searches and then get the Ids for Mysql query

Asked

Viewed 386 times

3

I’m thinking of the following idea: Use Elasticsearch features to make complex searches instead of making direct queries to Mysql using many where. If I were to use MYSQL I would have to create many indexes.

So when I give one insert in my table I will also put the record in Elasticsearch, would make this complex query in Elasticserch and take the id's to then query Mysql using for example:

SELECT * FROM table WHERE id IN (1,4,8,9,21,54,87,...)

That would be feasible?

mysql elasticsearch

  • is using some programming language in the middle?

  • Yes, I am using PHP, with Laravel 5.1 framework

  • You can then try to break into two parts your logic, in the first part make the query in Elastic and by Laravel use the whereIn of Eloquent

  • gmsants, that’s what I said in the question. I wonder if this is a good approach, if it would be worth doing so.

  • I believe so. I don’t know Elasticsearch very well yet, I intend to study it in a few weeks. So I don’t risk leaving any answers for now.

  • 1

    Look, it seems to me a great waste of resources. Elastic Search can now return everything you need, you should not search twice.

Show 1 more comment

1 answer

3


I use this way to make my queries because the relevance is much bigger and faster with the ids in hand I do the query in mysql to return a result:

WHERE produtos.id_produto IN (id1,id2,id...) 
ORDER BY FIELD(id1,id2,id...)

Use the order by field so you don’t lose the order of the Elasticsearch score.

Browser other questions tagged mysql elasticsearch

You are not signed in. Login or sign up in order to post.