Sparse Indexes

Sparse indexes only contain entries for documents that have the indexedfield, even if the index field contains a null value. The index skipsover any document that is missing the indexed field. The index is“sparse” because it does not include all documents of a collection. Bycontrast, non-sparse indexes contain all documents in a collection,storing null values for those documents that do not contain the indexedfield.

Important

Changed in version 3.2: Starting in MongoDB 3.2, MongoDB provides the option to createpartial indexes. Partial indexesoffer a superset of the functionality of sparse indexes. If youare using MongoDB 3.2 or later, partial indexes should be preferred over sparse indexes.

Create a Sparse Index

To create a sparse index, use thedb.collection.createIndex() method with the sparse optionset to true. For example, the following operation in themongo shell creates a sparse index on the xmpp_id fieldof the addresses collection:

  1. db.addresses.createIndex( { "xmpp_id": 1 }, { sparse: true } )

The index does not index documents that do not include the xmpp_idfield.

Note

Do not confuse sparse indexes in MongoDB with block-levelindexes in other databases. Think of them as dense indexes with aspecific filter.

Behavior

sparse Index and Incomplete Results

If a sparse index would result in an incomplete result set for queriesand sort operations, MongoDB will not use that index unless ahint() explicitly specifies the index.

For example, the query { x: { $exists: false } } will not use asparse index on the x field unless explicitly hinted. SeeSparse Index On A Collection Cannot Return Complete Results for an example that details thebehavior.

Changed in version 3.4.

If you include a hint() that specifies asparse index when you perform acount() of all documents in a collection (i.e. withan empty query predicate), the sparse index is used even if the sparseindex results in an incorrect count.

  1. db.collection.insert({ _id: 1, y: 1 } );
  2. db.collection.createIndex( { x: 1 }, { sparse: true } );
  3.  
  4. db.collection.find().hint( { x: 1 } ).count();

To obtain the correct count, do not hint() with asparse index when performing a count of alldocuments in a collection.

  1. db.collection.find().count();
  2.  
  3. db.collection.createIndex({ y: 1 });
  4. db.collection.find().hint({ y: 1 }).count();

Indexes that are sparse by Default

2dsphere (version 2), 2d,geoHaystack, and text indexes are always sparse.

sparse Compound Indexes

Sparse compound indexes that only containascending/descending index keys will index a document as long as thedocument contains at least one of the keys.

For sparse compound indexes that contain a geospatial key (i.e.2dsphere, 2d, orgeoHaystack index keys) along withascending/descending index key(s), only the existence of the geospatialfield(s) in a document determine whether the index references thedocument.

For sparse compound indexes that contain textindex keys along with ascending/descending index keys, only theexistence of the text index field(s) determine whether the indexreferences a document.

sparse and unique Properties

An index that is both sparse and uniqueprevents collection from having documents with duplicate values for afield but allows multiple documents that omit the key.

Examples

Create a Sparse Index On A Collection

Consider a collection scores that contains the following documents:

  1. { "_id" : ObjectId("523b6e32fb408eea0eec2647"), "userid" : "newbie" }
  2. { "_id" : ObjectId("523b6e61fb408eea0eec2648"), "userid" : "abby", "score" : 82 }
  3. { "_id" : ObjectId("523b6e6ffb408eea0eec2649"), "userid" : "nina", "score" : 90 }

The collection has a sparse index on the field score:

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

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

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

Because the document for the userid "newbie" does not contain thescore field and thus does not meet the query criteria, the querycan use the sparse index to return the results:

  1. { "_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:

  1. { "_id" : ObjectId("523b6e32fb408eea0eec2647"), "userid" : "newbie" }
  2. { "_id" : ObjectId("523b6e61fb408eea0eec2648"), "userid" : "abby", "score" : 82 }
  3. { "_id" : ObjectId("523b6e6ffb408eea0eec2649"), "userid" : "nina", "score" : 90 }

The collection has a sparse index on the field score:

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

Because the document for the userid "newbie" does not contain thescore field, the sparse index does not contain an entry for thatdocument.

Consider the following query to return all documents in the scorescollection, sorted by the score field:

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

Even though the sort is by the indexed field, MongoDB will notselect the sparse index to fulfill the query in order to returncomplete results:

  1. { "_id" : ObjectId("523b6e6ffb408eea0eec2649"), "userid" : "nina", "score" : 90 }
  2. { "_id" : ObjectId("523b6e61fb408eea0eec2648"), "userid" : "abby", "score" : 82 }
  3. { "_id" : ObjectId("523b6e32fb408eea0eec2647"), "userid" : "newbie" }

To use the sparse index, explicitly specify the index withhint():

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

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

  1. { "_id" : ObjectId("523b6e6ffb408eea0eec2649"), "userid" : "nina", "score" : 90 }
  2. { "_id" : ObjectId("523b6e61fb408eea0eec2648"), "userid" : "abby", "score" : 82 }

See also

explain() and Analyze Query Performance

Sparse Index with Unique Constraint

Consider a collection scores that contains the following documents:

  1. { "_id" : ObjectId("523b6e32fb408eea0eec2647"), "userid" : "newbie" }
  2. { "_id" : ObjectId("523b6e61fb408eea0eec2648"), "userid" : "abby", "score" : 82 }
  3. { "_id" : ObjectId("523b6e6ffb408eea0eec2649"), "userid" : "nina", "score" : 90 }

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

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

This index would permit the insertion of documents that had uniquevalues for the score field or did not include a score field.As such, given the existing documents in the scores collection, theindex permits the following insert operations:

  1. db.scores.insert( { "userid": "AAAAAAA", "score": 43 } )
  2. db.scores.insert( { "userid": "BBBBBBB", "score": 34 } )
  3. db.scores.insert( { "userid": "CCCCCCC" } )
  4. db.scores.insert( { "userid": "DDDDDDD" } )

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

  1. db.scores.insert( { "userid": "AAAAAAA", "score": 82 } )
  2. db.scores.insert( { "userid": "BBBBBBB", "score": 90 } )