Choose an index from a slow query rather than a guess

An API lists an organization's open tasks newest first. It is slow, so an assistant recommends an index on createdAt. That index matches the sort but ignores organization and status filters. The database may still examine many irrelevant records before finding the page. Another suggestion indexes every field independently, adding write cost without proving that the query can combine them effectively.

This example assumes MongoDB. The goal is to choose an index for one observed query, not produce a universal index recipe. Explain output and optimizer details vary by server version, so record the deployed version and inspect the fields actually returned rather than relying on a remembered stage name alone.

Capture the actual query shape

Write down equality predicates, range predicates, sort order, projection, limit, and pagination boundary. Include tenant scope and implicit ORM filters. A query in a notebook can differ from the request's real query if middleware adds soft-delete or access predicates.

Use representative synthetic data with multiple organizations and realistic status distribution. A tiny collection can make any index look adequate because there is little work to do. You do not need copied production records, but you do need distributions that exercise the selective and nonselective cases.

Record the symptom precisely: request latency, examined documents, examined keys, or an in-memory sort. Slow response time can also come from connection waits, N plus one reads, or serialization. Establish that this query is a meaningful contributor before changing indexes.

Read the plan as evidence

Run an explain with execution statistics in an isolated or appropriately controlled environment. Compare returned rows with examined documents and keys. Inspect whether the query performs a collection scan, uses an index, fetches documents, or sorts separately. Do not treat the presence of an index scan as proof that little work was done.

Execution statistics perform work, so use bounded representative requests rather than repeatedly explaining an expensive production query without considering impact. Record the query and dataset assumptions with the plan. A screenshot of one stage without context is hard to review.

Ask an AI assistant to interpret the specific plan and name what it cannot infer. It should not claim a certain latency improvement from key counts alone. Plans explain storage work; actual end-to-end latency also includes network, queueing, and application processing.

Propose a compound candidate

For equality on organization and status followed by descending creation time and identifier, a compound candidate can begin with those equality fields and include the ordering fields. The unique identifier tie-breaker supports the cursor contract.

await tasks.createIndex(
  { organizationId: 1, status: 1, createdAt: -1, _id: -1 },
  { name: 'organization_status_recent' }
)

This is a candidate to evaluate, not an instruction to install blindly. If the application often queries all statuses, the status position may affect reuse. If another predicate is a range, compare arrangements according to selectivity and sort needs. MongoDB's equality-sort-range guidance is a useful starting point, not a substitute for the actual plan.

Inspect existing indexes first. A new candidate may duplicate a prefix or overlap another index enough to require a cleanup decision. Do not drop an index until you know which other queries depend on it and have a rollback path.

Compare the candidate under relevant cases

Run the same query before and after in the controlled dataset. Compare examined work, sort behavior, and returned values. Test an organization with few tasks, one with many tasks, a common status, and a rare status. Include a late cursor page if that is where the symptom occurs.

A covered query can avoid document fetches when the index contains everything needed and other requirements are satisfied, but adding every response field can make the index unnecessarily large. Measure whether coverage is worth the storage and write cost for this endpoint.

If the query uses collation, partial indexes, sparse indexes, or multikey fields, verify compatibility carefully. A partial index only covers documents satisfying its predicate, and a query must be able to use it correctly. An assistant's generic compound example may ignore these details.

Plan installation and maintenance

Index creation is an operational change. Review collection size, build behavior, permissions, and deployment procedures for the server version. Name the index deliberately and verify its definition after creation. An ORM schema declaration is not evidence that the database has the index.

If the candidate is unique, audit existing data first because conflicts can block creation or require a data decision. That correctness case differs from the nonunique performance candidate here. Concurrent record uniqueness explains the audit.

Keep a bounded rollback plan for an index that causes unexpected write pressure or fails to help. The query can remain correct while its plan changes, so monitor the intended metric rather than declaring success solely because creation returned without error.

Verify application behavior too

An index should not change the API's result meaning. Check ordering, cursor continuation, visibility scope, and projection after the change. Test equal timestamps and missing optional values if they affect the query. A faster query that returns another tenant's rows is not an improvement.

Measure end-to-end behavior with a controlled workload and disclose its limits. Do not publish an invented benchmark or extrapolate a local result into a universal speedup. A useful report says which query, dataset, and metric changed and which factors remain unmeasured.

If response time remains slow while examined work falls, inspect the next bottleneck. N plus one diagnosis may reveal additional reads, and connection-pool waits may explain delay before the query begins.

Use AI to challenge the index rationale

Give the assistant the exact query, current indexes, explain statistics, and data distribution. Ask why each field appears in the candidate and which other query shape it may hurt or fail to support. Require it to distinguish a recommendation from a verified result.

Ask for counterexamples: what if status is omitted, the sort direction changes, or the range becomes broad? Those cases expose whether the candidate is narrowly appropriate or reusable. Do not let a model add speculative indexes for endpoints that have no measured problem.

Cursor pagination gives the ordering contract this example supports. The smallest safe index change follows that contract and the query plan, then earns its place through observed work and operational cost.

Keep the comparison dataset unchanged

When comparing plans, keep the fixture set and query values stable. Adding records between runs or warming one path while leaving the other cold can make timing comparisons misleading. Record whether the test measures cold or repeated execution and compare examined work independently from elapsed time. If using a hint to evaluate a candidate, verify the unhinted application query also selects an acceptable plan; a forced experimental plan is not proof of deployed behavior.