A query is slow. How do you diagnose it?

Short answer

Run explain('executionStats') and compare documents examined to documents returned. A large gap means extra scanning. COLLSCAN where you expected IXSCAN means no usable index. Also look for a SORT stage you hoped the index would cover.

Why?

The numbers that matter are totalDocsExamined, totalKeysExamined, nReturned, and executionTimeMillis. Examining far more keys than returned usually means a weak index. Do not add an index until you know which predicate is not using one. COLLSCAN is not automatically wrong on a tiny collection.

Example

db.orders.find({ status: "delivered" }).explain("executionStats")

Interview tip

Lead with examined-versus-returned. That is much stronger than 'I would add an index'.

Common mistake

Reading only executionTimeMillis in a quiet staging environment and calling the plan fine.