Create Indexes to Support Your Queries

An index supports a query when the index contains all the fields scannedby the query. The query scans the index and not the collection. Creating indexesthat support queries results in greatly increased query performance.

This document describes strategies for creating indexes that support queries.

Create a Single-Key Index if All Queries Use the Same, Single Key

If you only ever query on a single key in a given collection, then you needto create just one single-key index for that collection. For example, youmight create an index on category in the product collection:

  1. db.products.createIndex( { "category": 1 } )

Create Compound Indexes to Support Several Different Queries

If you sometimes query on only one key and at other times query on thatkey combined with a second key, then creating a compound index is moreefficient than creating a single-key index. MongoDB will use thecompound index for both queries. For example, you might create an indexon both category and item.

  1. db.products.createIndex( { "category": 1, "item": 1 } )

This allows you both options. You can query on just category, andyou also can query on category combined with item.A single compound index on multiple fieldscan support all the queries that search a “prefix” subset of those fields.

Example

The following index on a collection:

  1. { x: 1, y: 1, z: 1 }

Can support queries that the following indexes support:

  1. { x: 1 }
  2. { x: 1, y: 1 }

There are some situations where the prefix indexes may offer betterquery performance: for example if z is a large array.

The { x: 1, y: 1, z: 1 } index can also support many of the samequeries as the following index:

  1. { x: 1, z: 1 }

Also, { x: 1, z: 1 } has an additional use. Given the followingquery:

  1. db.collection.find( { x: 5 } ).sort( { z: 1} )

The { x: 1, z: 1 } index supports both the query and the sortoperation, while the { x: 1, y: 1, z: 1 } index only supportsthe query. For more information on sorting, seeUse Indexes to Sort Query Results.

Starting in version 2.6, MongoDB can use indexintersection to fulfill queries. The choicebetween creating compound indexes that support your queries or relyingon index intersection depends on the specifics of your system. SeeIndex Intersection and Compound Indexes for more details.

Index Use and Collation

To use an index for string comparisons, an operation must alsospecify the same collation. That is, an index with a collationcannot support an operation that performs string comparisons on theindexed fields if the operation specifies a different collation.

For example, the collection myColl has an index on a stringfield category with the collation locale "fr".

  1. db.myColl.createIndex( { category: 1 }, { collation: { locale: "fr" } } )

The following query operation, which specifies the same collation asthe index, can use the index:

  1. db.myColl.find( { category: "cafe" } ).collation( { locale: "fr" } )

However, the following query operation, which by default uses the“simple” binary collator, cannot use the index:

  1. db.myColl.find( { category: "cafe" } )

For a compound index where the index prefix keys are not strings,arrays, and embedded documents, an operation that specifies adifferent collation can still use the index to support comparisonson the index prefix keys.

For example, the collection myColl has a compound index on thenumeric fields score and price and the string fieldcategory; the index is created with the collation locale"fr" for string comparisons:

  1. db.myColl.createIndex(
  2.    { score: 1, price: 1, category: 1 },
  3.    { collation: { locale: "fr" } } )

The following operations, which use "simple" binary collationfor string comparisons, can use the index:

  1. db.myColl.find( { score: 5 } ).sort( { price: 1 } )
  2. db.myColl.find( { score: 5, price: { $gt: NumberDecimal( "10" ) } } ).sort( { price: 1 } )

The following operation, which uses "simple" binary collationfor string comparisons on the indexed category field, can usethe index to fulfill only the score: 5 portion of the query:

  1. db.myColl.find( { score: 5, category: "cafe" } )