Composite Indexes
A composite index is an index that has more than one indexed field and zero or more included fields. Composite indexes enable you to optimize queries that filter and sort on multiple fields. If all fields referenced in a query are either indexed or included fields in a composite index, then you can process the query by reading only the index.
Sort Order
HPE Ezmeral Data Fabric Database sorts the composite index in the order in which you have defined the indexed
fields. For example, if you have an index on Field1 and
Field2, HPE Ezmeral Data Fabric Database sorts on Field1 as the primary sort key
and Field2 as the secondary.
Each component in a composite index can have its own ordering. For example, you can specify an ascending sort order for one field and a descending sort order for another.
Composite Indexes and Container Field Paths
The indexed fields in a composite index can be Container Field Paths. However, if you specify more than one container field path in your indexed fields, the prefixes of the container field paths must be the same. This allows HPE Ezmeral Data Fabric Database to store index values that originate from the same array element in a single index row.
| Indexed Fields Allowed for Composite Index | Why Allowed? |
|---|---|
a[].b, x.z |
The indexed fields have only one container field path. |
a[].b, a[].c |
The indexed fields have a common container prefix,
a[]. |
a[].b, a[].c[] |
The indexed fields have a common container prefix,
a[]. |
a[].b[].c, a[].b[].d |
The indexed fields have a common container prefix, a[].b[]. |
If a composite index includes the same subfield in multiple indexed fields, the implied types of the subfields must also be consistent. The third and fourth rows in the following table show examples of this restriction:
| Indexed Fields Disallowed for Composite Index | Why Disallowed? |
|---|---|
a[].b, x[].y |
a[] and x[] are different container
prefixes. |
a[].b[], a[].c[] |
Although both indexed fields have a common container prefix of
a[], b[] and c[] are
different container field paths. |
a.b[].c, a.b.d |
In the first indexed field, subfield b is an array of nested
documents. In the second, b is a single nested document. This
results in a type conflict. |
a, a[] |
The first indexed field a is a scalar type while the second
is an array. This also results in a type conflict. |
When you have a composite index defined on container field paths, your query condition
must use the elementAnd operator to use all keys of the index. With the and
operator, the conditions do not have to match the same array element; as a result, the
matching indexed field values might span different index rows, preventing use of the
composite index. See OJAI Query Conditions Using elementAnd for more
details.
Composite Index Example
Consider the following example, which illustrates the impact of key order in a composite index:
In the index on the left, the prefix key is Field1. HPE Ezmeral Data Fabric Database sorts the index first on Field1, and then
on Field2. This aligns with the equality condition, Field1 =
3. HPE Ezmeral Data Fabric Database reads the least number of entries from
the index on the left, due to this equality condition and the range condition on
Field2.
In the index on the right, HPE Ezmeral Data Fabric Database sorts the index on Field2, followed by
Field1. In this case, the matching key values are not contiguous in the
index, as highlighted by the entries in a lighter shade of yellow. HPE Ezmeral Data Fabric Database uses the filter conditions on Field2 to
start the search in the index. It applies the filter condition on Field1
while reading the index. This is less efficient because HPE Ezmeral Data Fabric Database must read those extra non-matching key values.