Query and Index Optimization

Do not start with createIndex. Measure, then rewrite if the predicate cannot use an index, then index the shape that remains, then project and limit. That order stops you from indexing a regex that will never seek.

• 1. explain('executionStats') — examined vs returned, stage, SORT?
• 2. Rewrite. $in not a pile of $or on the same field. Prefix regex (/^Rahul/) can use an index; /keyboard/ cannot. $ne / $nin / $exists: false are weak. Equality on status plus range on amount beats range on both.
• 3. Index the leftover shape — ESR. { status: 1, amount: 1 } for delivered and amount >= 50000. One compound, not two singles.
• 4. Project and limit. Do not FETCH fields the API does not return. limit (and $match first in a pipeline) cuts work.

// can use { name: 1 }
db.products.find({ name: /^Aurora/ })

// cannot — leading wildcard
db.products.find({ name: { $regex: "keyboard" } })

If two indexes compete and the wrong one wins, fix selectivity or the compound prefix — hint is a last resort and a footgun when data changes. Aggregation: $match / $sort / $limit before $lookup and $group (Modules 6–7). A slow $lookup is often 'joined the whole collection' not 'need a magic lookup index' — though the foreign field should still be indexed.