Context
Adding an index on every field used by a query does not automatically produce an efficient query plan. A compound index has an ordered structure, and the useful access path depends on filters, sort requirements, and the shape of the data. The investigation should start with the actual query customers run, including its pagination and tenant boundary.
Consider an illustrative SaaS dashboard that lists a tenant's open tickets by creation time. The team adds an index after seeing the fields tenantId, status, and createdAt in the query. Small test accounts become fast, but large accounts remain slow. The next step is to inspect the execution evidence and data distribution, not add another index blindly.
Understand the prefix and sort relationship
MongoDB documents compound indexes as indexes over multiple fields, with field order and index prefixes affecting supported queries. Sort support also depends on the index and the query's constraints. An index containing the right field names is not sufficient evidence that it efficiently supports every filter and sort combination. 1
Record the complete query shape: equality filters, ranges, sort direction, projection, limit, and pagination method. Keep the tenant filter visible. A performance change must not remove the application's authorization boundary simply because a broader query is easier to index.
Collect representative execution evidence
Use the supported explain tooling for the deployed version and examine the selected plan, keys examined, documents examined, returned count, and any explicit sort stage. Keep actual execution of expensive queries bounded and authorized. A diagnostic query can itself create load if run repeatedly against a large collection.
Compare several representative tenants and data sizes. One tenant may contain most of the records or use a different distribution of statuses. Preserve sanitized query shapes rather than customer data. The useful evidence is the relationship between examined work and returned results, together with latency under comparable conditions.
Design around the leading access pattern
For a query that always constrains tenant and status by equality and sorts by creation time, evaluate a compound structure that supports that combination. Test it rather than treating a mnemonic as a universal answer. Other queries that omit status or use a range may need a different access path.
Do not assume one large compound index replaces every smaller index. Review which prefixes and sort patterns it supports and whether uniqueness, sparsity, partial filters, collation, or other options change the comparison. Index removal should have its own evidence about the queries that still depend on the candidate.
Include pagination in the performance review
A query can be fast on the first page and slow on later pages. Inspect how the application requests subsequent results, including any growing skip value or unstable sort. Use a deterministic ordering appropriate to the product, especially when several documents share the same timestamp.
If evaluating cursor-based pagination, define the ordering and continuation conditions carefully and test updates arriving between pages. Performance is only one part of correctness. Customers should not see unexplained duplicates or missing records because the application changed the paging contract without considering concurrent writes.
Measure index cost on the write path
Indexes consume storage and add work to writes. Observe insert and update latency, index size, cache pressure, and replication behaviour when introducing a candidate. A dashboard improvement can shift cost to ingestion or make a busy collection's working set harder to keep in memory.
Review the supported index-build procedure and capacity requirements for the deployment. Managed services and self-hosted replicas can have different operating controls. Plan the build during an appropriate window, monitor it, and define stop conditions if customer workload degrades. Avoid creating several overlapping candidates simultaneously without a clear comparison plan.
Test the query family, not one screenshot
List nearby query variants: all statuses, one status, a date range, reverse sort, an export, and a support search. Run representative checks for the variants the product supports. An index optimized for one path may leave another path unchanged or cause a planner choice that needs investigation.
Use hints only as a controlled diagnostic or a deliberately reviewed application choice. A forced index can become a long-term liability as data distribution changes. Keep the evidence showing why it is needed and a way to revisit it after major data growth or query changes.
Roll out with a visible baseline
Record the candidate definition, relevant query shapes, before-and-after execution metrics, build duration, and write-path impact. Observe the real workload after release. Query latency should be compared under similar load and cache conditions rather than selecting the best single run from each side.
An illustrative acceptance record might show that large-tenant ticket lists examine a bounded amount of work for each page while ingestion stays within its latency objective. The actual bound should reflect the query and dataset. Avoid promising a fixed speedup from a generic index recipe.
Keep index ownership connected to code
Store index definitions and their intended query families in the normal configuration or migration workflow. Review them when filters, sorting, or tenancy rules change. Retire unused indexes only after enough evidence covers infrequent jobs as well as daily traffic.
A focused operator exercise: A large-tenant query comparison
Build a test dataset with a small tenant and a much larger tenant, using harmless generated records and the real query shape. Compare the original and candidate indexes for the first page, a later page, and the supported date-range variant. Record examined work, returned results, sort behaviour, and comparable latency.
Run a bounded write workload during the comparison to expose the candidate's maintenance cost. If one index improves the main list but harms ingestion or leaves an important variant expensive, report that tradeoff rather than selecting the best isolated screenshot. Keep index options and data distribution with the result so the conclusion can be revisited after growth.
Quick diagnostic map
| Evidence | Review | Acceptance boundary |
|---|---|---|
| Query shape | Capture equality, sort and range requirements | Include representative tenant sizes |
| Index prefix | Compare candidate field order | Check which operations use the prefix |
| Execution evidence | Inspect examined keys, documents and sorting | Validate benefit against write overhead |
Frequently asked questions
Is the field list enough to choose an index? No. Query operators, order, sort, data distribution, and index options all influence the result.
Clouvenix can review MongoDB query shapes and execution plans together, helping teams improve customer-visible performance with a measured index design and an understood write cost.
For a scoped review of this workflow, discuss the relevant Clouvenix engineering service.