Implemented approach is to store all item fields in one string in format like:
{status}|{effectiveLocationId}|{materialTypeId}|{discoverySuppress}|{tags}
In process|53a7e309-e2fe-46da-8982-565809b297e8|81b08cf9-f103-42e2-873f-8383f355452d|true|resource|data|belief|sample|sea|version|implementation
Checked out|2c9717e7-110a-4c10-a20f-a5853bde3128|4949d136-b377-4f37-bb24-3de3ed05125d|true|belief|item|life
Elasticsearch index mappings for this field
{
...
"keyword": {
"mapping": {
"type": "keyword"
}
},
...
}
Elasticsearch queries were run locally against index with 2,5 millions of instances with 3-15 linked items with all fields. Index size - 13 Gb.
| | |
|---|
Status
| In process*
In process|*
Available*
Available|*
| ~370
~200
~190
~190
|
Location
| *e5d7f6f6-204f-4a55-b4e2-ce90db75c534*
| 2500-3900
|
Location/material
| *e5d7f6f6-204f-4a55-b4e2-ce90db75c534|0f89c322-5e94-44b3-a54c-aecfa767babc*
| ~2600
|
Tag
| *data*
| 3200-4700
|
Status/tag
| In process*life*
In process|*life*
| ~370
~200
|
Status/location
| In process|e5d7f6f6-204f-4a55-b4e2-ce90db75c534*
Available|e5d7f6f6-204f-4a55-b4e2-ce90db75c534*
| 35-60
50-70
|
Status/location/tag
| In process|5393b92a-5c67-4659-9d7a-41e685b385d6|*life*
Withdrawn|14ab6fa9-523e-40a2-a06a-5304a97393a3*hula-hu
| ~30
~80
|
Status/material/tag
| In process|*81b08cf9-f103-42e2-873f-8383f355452d|*life*
Available|*81b08cf9-f103-42e2-873f-8383f355452d|*life*
| ~185
~200
|
Material/tag
| *81b08cf9-f103-42e2-873f-8383f355452d*data*
| 2500-2800
|
Location/tag
| *e5d7f6f6-204f-4a55-b4e2-ce90db75c534*text
| 1300-1400
|
Full query
| Withdrawn|14ab6fa9-523e-40a2-a06a-5304a97393a3|0f89c322-5e94-44b3-a54c-aecfa767babc|false|sample|item
| 10-20
|