mongodb - Multiple Compound Indexes involving a common field

Question

We have a collection with millions of data. This data is being rendered in the UI for stats purpose and hence time to render is of key importance.

The queries to render the data involve the below fields:

1. field_a and field_t
2. field_b and field_t
3. field_c and field_t

As we are querying millions of data, we want to use Compound Index to speed up the queries.

To do so, we can simply add 3 different compound indexes as below:

db.mycollection.createIndex( { "field_a": 1, "field_t": 1 }
db.mycollection.createIndex( { "field_b": 1, "field_t": 1 }
db.mycollection.createIndex( { "field_c": 1, "field_t": 1 }

ESR rule is respected while creating the indexes as field_a, field_b and field_c are equality checks and field_t is a range check. Please note that field_t is common in all the 3 indexes.

Instead of creating 3 different indexes, is there a better approach to this?

Does mongo provide a more efficient way to handle this scenario where same field is being used in multiple compound indexes?

Answer 1

Better or more efficient in what regard?

Having the three indexes that you mentioned is the most efficient approach in terms of query performance. They will allow the database to process only the data that is relevant for each query and nothing else. Any other approach would reduce read efficiency (and speed) which may not be a good tradeoff.

Most databases, MongoDB included, typically use a single index when executing a query. This is mostly a consequence of how indexes work. Typically indexes use a B-tree like data structure, which is an ordered set of information. When following the ESR rule (placing equality conditions before range conditions), all of the information for a specific query is contained within a single bounded subtree in the index which can be directly traversed. It loses the ability to do this when the index is not structured in this way (including putting range keys first).

Other potential approaches using single field indexes would be things like:

• Index intersection - where you create (in this case) 4 single field indexes and have the database use 2 for each query. MongoDB typically does not choose this approach very often as it results in scanning larger portions of the index when compared to the compound index approach above.
• Using 1 single field index for each query - the database would end up retrieving documents to filter on the other field which could be quite inefficient depending on the selectivity of the other field.

While these may reduce the overall size of the collective indexes, it increases the cost (and decreases the efficiency) of executing the queries. Depending on what you are optimizing for, the approach you've outlined would be considered a best practice in terms of query efficiency.