Create Queries that Ensure Selectivity

Selectivity is the ability of a query to narrow results using the index.Effective indexes are more selective and allow MongoDB to use the indexfor a larger portion of the work associated with fulfilling the query.

To ensure selectivity,write queries that limit the number of possible documents with theindexed field. Write queries that are appropriately selective relativeto your indexed data.

Example

Suppose you have a field called status where the possible valuesare new and processed. If you add an index on statusyou’ve created a low-selectivity index. The index willbe of little help in locating records.

A better strategy, depending on your queries, would be to create acompound index that includes thelow-selectivity field and another field. For example, you couldcreate a compound index on status and created_at.

Another option, again depending on your use case, might be to useseparate collections, one for each status.

Example

Consider an index { a : 1 } (i.e. an index on the key asorted in ascending order) on a collection where a has threevalues evenly distributed across the collection:

{ _id: ObjectId(), a: 1, b: "ab" }
{ _id: ObjectId(), a: 1, b: "cd" }
{ _id: ObjectId(), a: 1, b: "ef" }
{ _id: ObjectId(), a: 2, b: "jk" }
{ _id: ObjectId(), a: 2, b: "lm" }
{ _id: ObjectId(), a: 2, b: "no" }
{ _id: ObjectId(), a: 3, b: "pq" }
{ _id: ObjectId(), a: 3, b: "rs" }
{ _id: ObjectId(), a: 3, b: "tv" }

If you query for { a: 2, b: "no" } MongoDB must scan 3documents in the collection to return the onematching result. Similarly, a query for { a: { $gt: 1}, b: "tv" }must scan 6 documents, also to return one result.

Consider the same index on a collection where a has nine valuesevenly distributed across the collection:

{ _id: ObjectId(), a: 1, b: "ab" }
{ _id: ObjectId(), a: 2, b: "cd" }
{ _id: ObjectId(), a: 3, b: "ef" }
{ _id: ObjectId(), a: 4, b: "jk" }
{ _id: ObjectId(), a: 5, b: "lm" }
{ _id: ObjectId(), a: 6, b: "no" }
{ _id: ObjectId(), a: 7, b: "pq" }
{ _id: ObjectId(), a: 8, b: "rs" }
{ _id: ObjectId(), a: 9, b: "tv" }

If you query for { a: 2, b: "cd" }, MongoDB must scan only onedocument to fulfill the query. The index and query are more selectivebecause the values of a are evenly distributed and the querycan select a specific document using the index.

However, although the index on a is more selective, a query suchas { a: { $gt: 5 }, b: "tv" } would still need to scan 4documents.

If overall selectivity is low, and if MongoDB must read a number ofdocuments to return results, then some queries may perform fasterwithout indexes. To determine performance, seeMeasure Index Use.