1

So, one of the backend system, having 5 resource types (tables) and some foreign keys as well, requires full text support for two of the tables.

Also, for some use-cases, we require inner-joins for some GET requests (depending on user-query).

What I wanted to understand is, for GET requests should we only rely on ElasticSearch, or depending on use-cases we should decide which data-source to pick?

Also, what should we do if user sends a query that requires both full-text-search and inner-joins?

Example: Models -

Product(id, title, description, brand_id)
Brand(id, name)
  • There can be paginated queries on products and brands -- MySQL works.
  • There can be paginated queries on products with brand_id as filter -- MySQL with where clause works.
  • There can be paginated queries on products where user also asks for corresponding brand_name -- MySQL with inner-join works.
  • There can be full text search on brand name, product title or description -- ElasticSearch suffices.

The problem occurs, or what I'm confused with is what do I do when the user wants full-text search on product title/description with brand_name of the product?

  • Option 1: Get everything from ElasticSearch
  • Option 2: Get a list of product.ids from ElasticSearch and then query MySQL
  • Option 3: Asynchronously fetch from ElasticSearch and MySQL

Problems I see with each of the approaches:

  • Option 1: Are joins even possible in ElasticSearch? Or do we index product with brand_name? If we do the latter, then is it possible to update brand_name in all product documents if a brand updates it's name in MySQL, using LogStash?
  • Option 2: Won't this end up being two queries running one after the other? One to fetch the list of product.ids from ES, and then the corresponding join-view from MySQL for those ids? Is this a good approach?
  • Option 3: If we fetch asynchronously, how will we manage pagination?
Improve this question
2
  • 1
    I recently had to think about a similar problem. I concluded that ES is just an index like in a book, where first you search the index, then you lookup the page. So I guess I'd go with option 2, but if that looks overkill... have you considered using the full text search features of MySQL? Commented May 1, 2022 at 9:43
  • 1
    @bigstones yup considered MySQL as well, might not work for us with growing requirements. Thanks for the reply (& opinion)! Commented May 4, 2022 at 7:19

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.