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
MapR-DB 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
, MapR-DB 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 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
. MapR-DB sorts the index first on Field1
, and then
on Field2
. This aligns with the equality condition, Field1 =
3
. MapR-DB 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, MapR-DB 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. MapR-DB 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 MapR-DB must read those extra nonmatching key values.