Index Properties

In addition to the numerous index types MongoDB supports, indexes can also have various properties. The following documents detail the index properties that you can select when building an index.

TTL Indexes – The TTL index is used for TTL collections, which expire data after a period of time.
Unique Indexes – A unique index causes MongoDB to reject all documents that contain a duplicate value for the indexed field.
Sparse Indexes – A sparse index does not index documents that do not have the indexed field.

TTL Indexes – TTL indexes are special indexes that MongoDB can use to automatically remove documents from a collection after a certain amount of time. This is ideal for some types of information like machine generated event data, logs, and session information that only need to persist in a database for a limited amount of time.

Considerations – TTL indexes have the following limitations

Compound indexes are not supported.
The indexed field must be a date type.
If the field holds an array, and there are multiple date-typed data in the index, the document will expire when the lowest (i.e. earliest) matches the expiration threshold.

The TTL index does not guarantee that expired data will be deleted immediately. There may be a delay between the time a document expires and the time that MongoDB removes the document from the database. The background task that removes expired documents runs every 60 seconds. As a result, documents may remain in a collection after they expire but before the background task runs or completes.

The duration of the removal operation depends on the workload of your mongod instance. Therefore, expired data may exist for some time beyond the 60 second period between runs of the background task. In all other respects, TTL indexes are normal indexes, and if appropriate, MongoDB can use these indexes to fulfill arbitrary queries.

Unique Indexes – A unique index causes MongoDB to reject all documents that contain a duplicate value for the indexed field. To create a unique index on the user_id field of the member’s collection, use the following operation in the mongo shell

db.addresses.ensureIndex( { “user_id”: 1 }, { unique: true } )

By default, unique is false on MongoDB indexes. If you use the unique constraint on a compound index, then MongoDB will enforce uniqueness on the combination of values rather than the individual value for any or all values of the key.

If a document does not have a value for the indexed field in a unique index, the index will store a null value for this document. Because of the unique constraint, MongoDB will only permit one document that lacks the indexed field. If there is more than one document without a value for the indexed field or is missing the indexed field, the index build will fail with a duplicate key error.

You can combine the unique constraint with the sparse index to filter these null values from the unique index and avoid the error. You may not specify a unique constraint on a hashed index.

Sparse Indexes – Sparse indexes only contain entries for documents that have the indexed field, even if the index field contains a null value. The index skips over any document that is missing the indexed field. The index is “sparse” because it does not include all documents of a collection. By contrast, non-sparse indexes contain all documents in a collection, storing null values for those documents that do not contain the indexed field. The following example in the mongo shell creates a sparse index on the xmpp_id field of the addresses collection

db.addresses.ensureIndex( { “xmpp_id”: 1 }, { sparse: true } )

By default, sparse is false on MongoDB indexes. But change made in version 2.6, If a sparse index results in an incomplete result set for queries and sort operations, MongoDB will not use that index unless a hint() explicitly specifies the index. For example, the query { x: { $exists: false } } will not use a sparse index on the x field unless explicitly hinted. For 2dsphere indexes (version 2), MongoDB ignores the sparse flag.

Do not confuse sparse indexes in MongoDB with block-level indexes in other databases. Think of them as dense indexes with a specific filter. You can specify a sparse and unique index that rejects documents that have duplicate values for a field, but allows multiple documents that omit that key.

Examples

Create a Sparse Index On A Collection – Consider a collection scores that contains the following documents:

{ “_id” : ObjectId(“523b6e32fb408eea0eec2647”), “userid” : “newbie” }

{ “_id” : ObjectId(“523b6e61fb408eea0eec2648”), “userid” : “abby”, “score” : 82 }

{ “_id” : ObjectId(“523b6e6ffb408eea0eec2649”), “userid” : “nina”, “score” : 90 }

The collection has a sparse index on the field score:

db.scores.ensureIndex( { score: 1 } , { sparse: true } )

Then, the following query on the scores collection uses the sparse index to return the documents that have the score field less than ($lt) 90

db.scores.find( { score: { $lt: 90 } } )

Because the document for the userid “newbie” does not contain the score field and thus does not meet the query criteria, the query can use the sparse index to return the results:

{ “_id” : ObjectId(“523b6e61fb408eea0eec2648”), “userid” : “abby”, “score” : 82 }

Sparse Index On A Collection Cannot Return Complete Results – Consider a collection scores that contains the following documents:

{ “_id” : ObjectId(“523b6e32fb408eea0eec2647”), “userid” : “newbie” }

{ “_id” : ObjectId(“523b6e61fb408eea0eec2648”), “userid” : “abby”, “score” : 82 }

{ “_id” : ObjectId(“523b6e6ffb408eea0eec2649”), “userid” : “nina”, “score” : 90 }

The collection has a sparse index on the field score

db.scores.ensureIndex( { score: 1 } , { sparse: true } )

Because the document for the userid “newbie” does not contain the score field, the sparse index does not contain an entry for that document.

Consider the following query to return all documents in the scores collection, sorted by the score field

db.scores.find().sort( { score: -1 } )

Even though the sort is by the indexed field, MongoDB will not select the sparse index to fulfill the query in order to return complete results:

{ “_id” : ObjectId(“523b6e6ffb408eea0eec2649”), “userid” : “nina”, “score” : 90 }

{ “_id” : ObjectId(“523b6e61fb408eea0eec2648”), “userid” : “abby”, “score” : 82 }

{ “_id” : ObjectId(“523b6e32fb408eea0eec2647”), “userid” : “newbie” }

To use the sparse index, explicitly specify the index with hint():

db.scores.find().sort( { score: -1 } ).hint( { score: 1 } )

The use of the index results in the return of only those documents with the score field:

{ “_id” : objectId(“523b6e6ffb408eea0eec2649”), “userid” : “nina”, “score” : 90 }

{ “_id” : ObjectId(“523b6e61fb408eea0eec2648”), “userid” : “abby”, “score” : 82 }

Sparse Index with Unique Constraint – Consider a collection scores that contains the following documents

{ “_id” : ObjectId(“523b6e32fb408eea0eec2647”), “userid” : “newbie” }

{ “_id” : ObjectId(“523b6e61fb408eea0eec2648”), “userid” : “abby”, “score” : 82 }

{ “_id” : ObjectId(“523b6e6ffb408eea0eec2649”), “userid” : “nina”, “score” : 90 }

You could create an index with a unique constraint and sparse filter on the score field using the following operation:

db.scores.ensureIndex( { score: 1 } , { sparse: true, unique: true } )

This index would permit the insertion of documents that had unique values for the score field or did not include a score field. Consider the following insert operation:

db.scores.insert( { “userid”: “AAAAAAA”, “score”: 43 } )

db.scores.insert( { “userid”: “BBBBBBB”, “score”: 34 } )

db.scores.insert( { “userid”: “CCCCCCC” } )

db.scores.insert( { “userid”: “DDDDDDD” } )

However, the index would not permit the addition of the following documents since documents already exists with score value of 82 and 90

db.scores.insert( { “userid”: “AAAAAAA”, “score”: 82 } )

db.scores.insert( { “userid”: “BBBBBBB”, “score”: 90 } )