How to Optimize ElasticSearch Index Structure for Multi-Entity Relationships from a Legacy Database?

Configuring ElasticSearch Index Structure with Multiple Entity Bindings

When integrating ElasticSearch (ES) with a legacy database, it's important to optimize the index structure for efficient queries. In this case, the task is to replicate a database with a complex, non-optimal structure into ES.

Problem Outline

The database comprises three tables:

• Products: Contains product data (id, title, price)
• Flags: Contains flag definitions (sellout, discount, topproduct)
• FlagsProducts: A pivot table linking Products and Flags

The goal is to flatten this structure for easier querying in ES, while preserving the multi-valued relationships.

Solution: Flattening the Structure

To eliminate the n:m relationship between products and flags, we recommend creating a flat product documents that include a "flags" array containing all relevant flags for each product. This approach simplifies queries for flags and provides a more coherent data structure.

Here are sample flattened product documents:

{
   "id": "00c8234d71c4e94f725cd432ebc04",
   "title": "Alpha",
   "price": 589.0,
   "flags": ["Sellout", "Top Product"]
}

{
   "id": "018357657529fef056cf396626812",
   "title": "Beta",
   "price": 355.0,
   "flags": ["Discount"]
}

{
   "id": "01a2c32ceeff0fc6b7dd4fc4302ab",
   "title": "Gamma",
   "price": 0.0,
   "flags": ["Discount"]
}

ES Mapping

The corresponding mapping for the "products" index type would be:

PUT products
{
    "mappings": {
        "product": {
            "properties": {
                "id": {
                    "type": "string",
                    "index": "not_analyzed"
                },
                "title": {
                    "type": "string"
                },
                "price": {
                    "type": "double",
                    "null_value": 0.0
                },
                "flags": {
                    "type": "string",
                    "index": "not_analyzed"
                }
            }
        }
    }
}

SQL Query for Logstash Input

To fetch the required data from the database, we suggest the following SQL query:

The above is the detailed content of How to Optimize ElasticSearch Index Structure for Multi-Entity Relationships from a Legacy Database?. For more information, please follow other related articles on the PHP Chinese website!