OPTIONS

Indexing Operations

This document provides operational guidelines and procedures for indexing data in MongoDB collections. For the fundamentals of MongoDB indexing, see the Indexing Overview document. For strategies and practical approaches, see the Indexing Strategies document.

Indexes allow MongoDB to process and fulfill queries quickly by creating small and efficient representations of the documents in a collection.

Create an Index

To create an index, use db.collection.ensureIndex() or a similar method from your driver. For example the following creates an index on the phone-number field of the people collection:

db.people.ensureIndex( { "phone-number": 1 } )

ensureIndex() only creates an index if an index of the same specification does not already exist.

All indexes support and optimize the performance for queries that select on this field. For queries that cannot use an index, MongoDB must scan all documents in a collection for documents that match the query.

Example

If you create an index on the user_id field in the records, this index is, the index will support the following query:

db.records.find( { user_id: 2 } )

However, the following query, on the profile_url field is not supported by this index:

db.records.find( { profile_url: 2 } )

If your collection holds a large amount of data, consider building the index in the background, as described in Background Construction. To build indexes on replica sets, see the Build Indexes on Replica Sets section for more information.

Create a Compound Index

To create a compound index use an operation that resembles the following prototype:

db.collection.ensureIndex( { a: 1, b: 1, c: 1 } )

For example, the following operation will create an index on the item, category, and price fields of the products collection:

db.products.ensureIndex( { item: 1, category: 1, price: 1 } )

Some drivers may specify indexes, using NumberLong(1) rather than 1 as the specification. This does not have any affect on the resulting index.

Note

To build or rebuild indexes for a replica set see Build Indexes on Replica Sets.

If your collection is large, build the index in the background, as described in Background Construction. If you build in the background on a live replica set, see also Build Indexes on Replica Sets.

Special Creation Options

Note

TTL collections use a special expire index option. See Expire Data from Collections by Setting TTL for more information.

Note

To create geospatial indexes, see Create a Geospatial Index.

Sparse Indexes

To create a sparse index on a field, use an operation that resembles the following prototype:

db.collection.ensureIndex( { a: 1 }, { sparse: true } )

The following example creates a sparse index on the users table that only indexes the twitter_name if a document has this field. This index will not include documents in this collection without the twitter_name field.

db.users.ensureIndex( { twitter_name: 1 }, { sparse: true } )

Note

Sparse indexes can affect the results returned by the query, particularly with respect to sorts on fields not included in the index. See the sparse index section for more information.

Unique Indexes

To create a unique indexes, consider the following prototype:

db.collection.ensureIndex( { a: 1 }, { unique: true } )

For example, you may want to create a unique index on the "tax-id": of the accounts collection to prevent storing multiple account records for the same legal entity:

db.accounts.ensureIndex( { "tax-id": 1 }, { unique: true } )

The _id index is a unique index. In some situations you may consider using _id field itself for this kind of data rather than using a unique index on another field.

In many situations you will want to combine the unique constraint with the sparse option. When MongoDB indexes a field, if a document does not have a value for a field, the index entry for that item will be null. Since unique indexes cannot have duplicate values for a field, without the sparse option, MongoDB will reject the second document and all subsequent documents without the indexed field. Consider the following prototype.

db.collection.ensureIndex( { a: 1 }, { unique: true, sparse: true } )

You can also enforce a unique constraint on compound indexes, as in the following prototype:

db.collection.ensureIndex( { a: 1, b: 1 }, { unique: true } )

These indexes enforce uniqueness for the combination of index keys and not for either key individually.

Create in Background

To create an index in the background you can specify background construction. Consider the following prototype invocation of db.collection.ensureIndex():

db.collection.ensureIndex( { a: 1 }, { background: true } )

Consider the section on background index construction for more information about these indexes and their implications.

Drop Duplicates

To force the creation of a unique index index on a collection with duplicate values in the field you are indexing you can use the dropDups option. This will force MongoDB to create a unique index by deleting documents with duplicate values when building the index. Consider the following prototype invocation of db.collection.ensureIndex():

db.collection.ensureIndex( { a: 1 }, { dropDups: true } )

See the full documentation of duplicate dropping for more information.

Warning

Specifying { dropDups: true } may delete data from your database. Use with extreme caution.

Refer to the ensureIndex() documentation for additional index creation options.

Information about Indexes

List all Indexes on a Collection

To return a list of all indexes on a collection, use the, use the db.collection.getIndexes() method or a similar method for your driver.

For example, to view all indexes on the people collection:

db.people.getIndexes()

List all Indexes for a Database

To return a list of all indexes on all collections in a database, use the following operation in the mongo shell:

db.system.indexes.find()

Measure Index Use

Query performance is a good general indicator of index use; however, for more precise insight into index use, MongoDB provides the following tools:

  • explain()

    Append the explain() method to any cursor (e.g. query) to return a document with statistics about the query process, including the index used, the number of documents scanned, and the time the query takes to process in milliseconds.

  • cursor.hint()

    Append the hint() to any cursor (e.g. query) with the index as the argument to force MongoDB to use a specific index to fulfill the query. Consider the following example:

    db.people.find( { name: "John Doe", zipcode: { $gt: 63000 } } } ).hint( { zipcode: 1 } )
    

    You can use hint() and explain() in conjunction with each other to compare the effectiveness of a specific index. Specify the $natural operator to the hint() method to prevent MongoDB from using any index:

    db.people.find( { name: "John Doe", zipcode: { $gt: 63000 } } } ).hint( { $natural: 1 } )
    
  • indexCounters

    Use the indexCounters data in the output of serverStatus for insight into database-wise index utilization.

Remove Indexes

To remove an index, use the db.collection.dropIndex() method, as in the following example:

db.accounts.dropIndex( { "tax-id": 1 } )

This will remove the index on the "tax-id" field in the accounts collection. The shell provides the following document after completing the operation:

{ "nIndexesWas" : 3, "ok" : 1 }

Where the value of nIndexesWas reflects the number of indexes before removing this index. You can also use the db.collection.dropIndexes() to remove all indexes, except for the _id index from a collection.

These shell helpers provide wrappers around the dropIndexes database command. Your client library may have a different or additional interface for these operations.

Rebuild Indexes

If you need to rebuild indexes for a collection you can use the db.collection.reIndex() method. This will drop all indexes, including the _id index, and then rebuild all indexes. The operation takes the following form:

db.accounts.reIndex()

MongoDB will return the following document when the operation completes:

{
        "nIndexesWas" : 2,
        "msg" : "indexes dropped for collection",
        "nIndexes" : 2,
        "indexes" : [
                {
                        "key" : {
                                "_id" : 1,
                                "tax-id" : 1
                        },
                        "ns" : "records.accounts",
                        "name" : "_id_"
                }
        ],
        "ok" : 1
}

This shell helper provides a wrapper around the reIndex database command. Your client library may have a different or additional interface for this operation.

Note

To build or rebuild indexes for a replica set see Build Indexes on Replica Sets.

Build Indexes on Replica Sets

Background index creation operations become foreground indexing operations on secondary members of replica sets. The foreground index building process blocks all replication and read operations on the secondaries while they build the index.

Secondaries will begin building indexes after the primary finishes building the index. In sharded clusters, the mongos will send ensureIndex() to the primary members of the replica set for each shard, which then replicate to the secondaries after the primary finishes building the index.

To minimize the impact of building an index on your replica set, use the following procedure to build indexes on secondaries:

Note

If you need to build an index in a sharded cluster, repeat the following procedure for each replica set that provides each shard.

  1. Stop the mongod process on one secondary. Restart the mongod process without the --replSet option and running on a different port. [1] This instance is now in “standalone” mode.
  2. Create the new index or rebuild the index on this mongod instance.
  3. Restart the mongod instance with the --replSet option. Allow replication to catch up on this member.
  4. Repeat this operation on all of the remaining secondaries.
  5. Run rs.stepDown() on the primary member of the set, and then repeat this procedure on the former primary.

Warning

Ensure that your oplog is large enough to permit the indexing or re-indexing operation to complete without falling too far behind to catch up. See the “oplog sizing” documentation for additional information.

Note

This procedure does take one member out of the replica set at a time. However, this procedure will only affect one member of the set at a time rather than all secondaries at the same time.

[1]By running the mongod on a different port, you ensure that the other members of the replica set and all clients will not contact the member while you are building the index.

Monitor and Control Index Building

To see the status of the indexing processes, you can use the db.currentOp() method in the mongo shell. The value of the query field and the msg field will indicate if the operation is an index build. The msg field also indicates the percent of the build that is complete.

You can only terminate a background index build. If you need to terminate an ongoing index build, You can use the db.killOp() method in the mongo shell.