6

Is it possible to query across multiple Indices with regard to relations? I know I can search across multiple Indices, but then I get two disjoint result sets.

For example I have two indices "customer" and "address" that are related by an address-key in the customer index. I want to get all customers named "John" that live in "New York" in one query. Can ES "join" the two result sets from address and customer and give me all Johns from NY?

I get the Feeling ES isn't made for that rdbms-style joining of Indices. Unfortunately flattening the indices into one index isn't an Option. Querying multiple times is also not an option.

Yu Hao
  • 119,891
  • 44
  • 235
  • 294
The Vic
  • 63
  • 1
  • 4
  • 3
    That's a design problem. I'm curious why you cannot have the address indexed into the customer document? – Val Mar 29 '18 at 08:41
  • The address-customer-problem was just a very simple example. My boss wants to replace every database query with Elasticsearch. We have queries joining over 20 tables with more than 60 Parameters. Seems like I'm the one who has to tell him we can't do that. – The Vic Mar 29 '18 at 11:05
  • 1
    Then my answer is always the same: https://stackoverflow.com/questions/36915428/how-to-setup-elasticsearch-index-structure-with-multiple-entity-bindings/36982705#36982705 (hint: denormalize or continue using a RDBMS) – Val Mar 29 '18 at 11:08
  • Note that it is perfectly possible to keep both MySQL and ES, leaving relationships in MySQL and use ES for efficient and blazzing fast querying. – Val Mar 29 '18 at 11:09

1 Answers1

4

ES is document based No-SQL, so you will not really be able to do joins natively, it is not relational: that is the whole point.

Performing full SQL-style joins in a distributed system like Elasticsearch is prohibitively expensive. Instead, Elasticsearch offers two forms of join which are designed to scale horizontally.

https://www.elastic.co/guide/en/elasticsearch/reference/current/joining-queries.html

An efficient solution is to duplicate data, also called denormalizing data, this avoids doing joins but will be heavier in terms of storage and code logic.

The way to get the best search performance out of Elasticsearch is to use it as it is intended, by denormalizing your data at index time. Having redundant copies of data in each document that requires access to it removes the need for joins.

Read: https://www.elastic.co/guide/en/elasticsearch/guide/current/denormalization.html

If you want optimal search speed rebuild denormalized indexes from the existing ones.

Christophe Roussy
  • 16,299
  • 4
  • 85
  • 85