Efficient document structure

[maxheyer]

Hello,
we want to use elastic search for e-commerce statistics. In our web view we have the possibility to filter products by sales, attributes like colors, categories, full text search etc.

The result is a table of products with statistics on sales, inventory changes and product attributes.

With the current implementation we calculate sales kpis on indexing for a period of 90 days, but in the future we want to calculate them on-the-fly. Our mysql schema looks like this:

Product models 1:n Product variants 1:n Product units
Orders 1:n Order lines n:n Product units

For your information: The table of order lines has 6 million lines and a total size of 2 GB. The product tables look similar.

Is there an smart way to index all data with relations in the Elastik search? For example, should we store all data in one document: A product document has all attributes and a list of all orders and stock changes?

Or should we split it into different documents? Is there a possibility to join them then?

[wangqinghuan (Wang)]

There is only limited support for Join in Elasticsearch. If using Elasticsearch, you should de-normalize the dimension table directly into the fact table to avoid join.

[fadjar340 (Fadjar Tandabawana)]

Use View or Materialized View with all of the data that oyu interested into de-normalized, then ingest the view using pagination and timestamp > :sql_last_value to avoid the abuse of select query.

[nik9000 (Nik Everett)]

If your orders are already in mysql I'd leave then there. I'd calculate what you need in MySQL and dump those fields into the document. ES stores the whole document together for fast retrieval of all fields. So denormalizing all the orders into the document would make loading slow.