Follow the ESR ruleUse Covered Queries When PossibleUse Caution When Considering Indexes on Low-Cardinality FieldsUse Partial IndexesComparison with the sparse IndexAvoid Regular Expressions That Are Not Left Anchored or RootedAvoid Case Insensitive Regular Expressions
Follow the ESR rule
For compound indexes, this rule of thumb is helpful in deciding the order of fields in the index:
- First, add those fields against which Equality queries are run.
- The next fields to be indexed should reflect the Sort order of the query.
- The last fields represent the Range of data to be accessed.
Use Covered Queries When Possible
Covered queries return results from an index directly without having to access the source documents, and are therefore very efficient.
For a query to be covered all the fields needed for filtering, sorting and/or being returned to the client must be present in an index. To determine whether a query is a covered query, use the
explain() method. If the explain() output displays totalDocsExamined as 0, this shows the query is covered by an index. Read more in the documentation for explain results.A common gotcha when trying to achieve covered queries is that the _id field is always returned by default. You need to explicitly exclude it from query results, or add it to the index.
In sharded clusters, MongoDB internally needs to access the fields of the shard key. This means covered queries are only possible when the shard key is part of the index. It is usually a good idea to do this anyway.
Use Caution When Considering Indexes on Low-Cardinality Fields
Queries on fields with a small number of unique values (low cardinality) can return large result sets. Compound indexes may include fields with low cardinality, but the value of the combined fields should exhibit high cardinality.
Use Partial Indexes
Reduce the size and performance overhead of indexes by only including documents that will be accessed through the index. For example, create a partial index on the
orderID field that only includes order documents with an orderStatus of "In progress", or only indexes the emailAddress field for documents where it exists.Comparison with the sparse Index
Partial indexes represent a superset of the functionality offered by sparse indexes and should be preferred over sparse indexes.
Partial indexes offer a more expressive mechanism than Sparse Indexes indexes to specify which documents are indexed.
Sparse indexes select documents to index solely based on the existence of the indexed field, or for compound indexes, the existence of the indexed fields.
Partial indexes determine the index entries based on the specified filter. The filter can include fields other than the index keys and can specify conditions other than just an existence check.
However, a partial index can also specify filter expressions on fields other than the index key. For example, the following operation creates a partial index, where the index is on the
name field but the filter expression is on the email fieldFor the query optimizer to choose this partial index, the query predicate must include a condition on the
name field as well as a non-null match on the email field.For example, the following query can use the index because it includes both a condition on the
name field and a non-null match on the email fielddb.contacts.find( { name: "xyz", email: { $regex: /\.org$/ } } )Avoid Regular Expressions That Are Not Left Anchored or Rooted
Indexes are ordered by value. Leading wildcards are inefficient and may result in full index scans. Trailing wildcards can be efficient if there are sufficient case-sensitive leading characters in the expression.
Avoid Case Insensitive Regular Expressions
If the sole reason for using a regex is case insensitivity, use a case insensitive index instead, as those are faster.