Code Summary: Sparse Indexes

The code in this video uses some sample documents that you can insert into your MongoDB instance or Atlas Cluster with the following command in mongosh:

db.getSiblingDB("sample_db").sparseExample.insertMany([
  {
    _id: new ObjectId("64920144bf3922c17f7181ca"),
    username: "coolUser",
    avatar_url: "https://api.multiavatar.com/coolUser.svg",
  },
  {
    _id: new ObjectId("64920144bf3922c17f7181cb"),
    username: "testUser",
    avatar_url: "https://api.multiavatar.com/testUser.svg",
  },
  {
    _id: new ObjectId("64920144bf3922c17f7181cc"),
    username: "anotherUser",
    avatar_url: "https://api.multiavatar.com/anotherUser.svg",
  },
  { _id: new ObjectId("64920173bf3922c17f7181cd"), username: "test" },
]);

To create a sparse index on the avatar_url field use the createIndex method and provide an index specification document containing the field we want to index. For the options object, add an object with a field sparse and a value of true, as shown in the following command:

db.sparseExample.createIndex({ avatar_url: 1 }, { sparse: true })

To test the index, run a query that looks for all documents in the sparseExample collection that contain an avatar image. A total of three documents should be returned:

db.sparseExample.find({ avatar_url: { $exists: true } })

To see if the query is using the sparse index, use explain with the executionStats verbosity level. This command uses dot notation to return just the executionStats object from the explain output:

db.sparseExample.find({ avatar_url: { $exists: true } }).explain("executionStats").executionStats

Note In addition to the index being listed in executionStages, the isSparse field in the execution stats output should also have a value of true indicating that the index was used.

To test the sparse index one more time, run the following command to get the explain output and return just the executionStats object for a query that finds all documents sorted by avatar_url:

db.sparseExample.find().sort({ avatar_url: 1 }).explain("executionStats").executionStats

Note that the sparse index wasn’t used since using it would lead to incomplete results