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?
  • 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?
    – bigstones
    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)!
    – Bugs Buggy
    Commented May 4, 2022 at 7:19


Your Answer

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