mongoose: compound index

Previously, I wrote about adding indexes to Mongo databases manually.

I’m working with Mongoose now and wanted to understand what was involved.

The Mongoose documentation is good, albeit a bit bare. Mongo’s documentation on indexing is better.

Let’s add a few indexes to a schema for a Todo document to see how they might work:

import mongoose from "mongoose"
import { Todo } from "./todos.model"

export const todoSchema = new mongoose.Schema<Todo>(
  {
    name: { type: String, required: true, index: true },
    done: Boolean,
    notes: { type: String, unique: true },
    references: { type: String, sparse: true },
    due: Date,
    createdBy: {
      type: mongoose.SchemaTypes.ObjectId,
      ref: "user",
      required: true,
    },
    status: {
      type: String,
      required: true,
      enum: ["Active", "Past Due", "Completed"],
    },
    list: {
      type: mongoose.SchemaTypes.ObjectId,
      ref: "list",
      required: true,
    },
  },
  { timestamps: true },
)

todoSchema.index({ list: 1, name: -1 }, { unique: true })

export const TodoModel = mongoose.model("Todo", todoSchema)

This is more demonstrative that reasonable. I’ve included fields that likely wouldn’t have indexes (notes, references) and created a redundant index on name.

So, what’s included?

1. name has its own index
2. notes gets an index by virtue of the unique constraint.
3. references is a sparse index.
4. Finally, there’s a compund index on list and name.

A few notes on the syntax for the compound index:

1. The numbers indicate the order of the index (list is ascending (1), while name is descending (-1)).

2. In this case, we’re saying there’s a unique constraint on the combination of the list and name for a Todo.

3. The name index on the model is redundant since it’s a “standard” index. From Mongo:

   If you have a collection that has both a compound index and an index on its prefix (e.g. { a: 1, b: 1 } and { a: 1 }), if neither index has a sparse or unique constraint, then you can remove the index on the prefix (e.g. { a: 1 }). MongoDB will use the compound index in all of the situations that it would have used the prefix index.