MongoDB indexes from C#, and how to know you need one

MongoDB is quick to start with: no schema to define, no migrations to run. The downside is that it never complains about a missing index. A query on an unindexed field still works. It just reads every document in the collection to find the ones that match, and that only becomes a problem once the collection is big.

#Create indexes from code

The C# driver can create indexes with strongly typed definitions:

var orders = database.GetCollection<Order>("orders");

var byCustomerAndDate = new CreateIndexModel<Order>(
    Builders<Order>.IndexKeys
        .Ascending(o => o.CustomerId)
        .Descending(o => o.CreatedAt),
    new CreateIndexOptions { Name = "customer_createdAt" });

var uniqueExternalId = new CreateIndexModel<Order>(
    Builders<Order>.IndexKeys.Ascending(o => o.ExternalId),
    new CreateIndexOptions { Name = "externalId_unique", Unique = true });

await orders.Indexes.CreateManyAsync([byCustomerAndDate, uniqueExternalId], ct);

Creating an index that already exists with the same definition does nothing, so running this at startup or from a deployment step is safe. For large collections, prefer a deployment step, since building a new index takes time and resources.

#Order compound index fields well

For a compound index, the order of fields matters. A useful guideline is ESR: Equality, Sort, Range:

1. Fields you match exactly first, such as CustomerId
2. Then fields you sort by, such as CreatedAt
3. Then fields you filter by range, such as Total > 100

The index above serves this query perfectly:

var recent = await orders
    .Find(o => o.CustomerId == customerId)
    .SortByDescending(o => o.CreatedAt)
    .Limit(20)
    .ToListAsync(ct);

MongoDB finds the customer's entries in the index, already in date order, and stops after 20. No sorting in memory and no scanning.

#Check that the index is used

Ask MongoDB how it runs the query. In mongosh or MongoDB Compass:

db.orders.find({ CustomerId: "C-42" }).sort({ CreatedAt: -1 }).limit(20).explain("executionStats")

Look for:

• IXSCAN in the winning plan: an index was used. COLLSCAN means a full collection scan.
• totalDocsExamined close to nReturned. Examining 50,000 documents to return 20 means the index doesn't fit the query.

If you use MongoDB Atlas, its Performance Advisor watches slow queries and suggests indexes for them.

#Useful index options

• Unique indexes enforce business rules, such as one order per external ID, and protect you from duplicate inserts.
• TTL indexes delete documents automatically after a period, which is ideal for logs, idempotency keys and other temporary data: new CreateIndexOptions { ExpireAfter = TimeSpan.FromDays(7) } on a date field.

#Don't index everything

Every index has to be updated on every write and takes memory. Index the queries your application actually runs often, and remove indexes that aren't used.

#Takeaway

Create indexes from code for the queries you run often, order compound index fields by equality, then sort, then range, and use explain to confirm queries use them. Unique and TTL indexes also enforce rules and clean up data for you.