Working with Compound Indexes

1. Creating Compound Index

Form	Effect
createIndex({a:1, b:-1})	Two-key index
Max keys	32 fields per compound index

2. Understanding Index Order

Principle	Effect
Equality first	Most-selective equality fields first
Sort next	Then sort fields in sort order
Range last	Range/inequality fields last
Rule	"ESR" rule: Equality, Sort, Range

3. Using Prefix Queries

Index	Supported queries
{a:1, b:1, c:1}	{a}, {a,b}, {a,b,c}
Not supported	{b} alone, {c} alone, {b,c}

4. Understanding Sort Direction Optimization

Index	Compatible sort
{a:1, b:-1}	sort({a:1,b:-1}) or sort({a:-1,b:1})
Not compatible	sort({a:1,b:1})
Reason	Index can be scanned forward or backward, not mixed per key

5. Optimizing Compound Index for Queries

Goal	Strategy
Single index per query pattern	Design index matching query+sort exactly
Avoid duplicates	Compound {a,b} covers {a}; don't add separate {a}
Selectivity	Most selective field early reduces scanned keys

6. Setting Index Options

Option	Effect
unique	Enforce uniqueness on combination
partialFilterExpression	Index subset of docs
collation	String comparison rules
hidden	Hide from planner (test impact)
name	Custom name

7. Understanding Covered Queries

Requirement	Detail
All query fields indexed	Filter only uses indexed fields
All projected fields indexed	No fetch from collection
_id excluded	Unless _id is in index
No arrays in index	Multikey indexes can't cover
Indicator	explain shows totalDocsExamined: 0

8. Analyzing Compound Index Performance

Metric (explain)	Goal
totalKeysExamined	Close to nReturned
totalDocsExamined	0 (covered) or close to nReturned
stage	IXSCAN (not COLLSCAN)
SORT_KEY_GENERATOR	Absent = index-supported sort

9. Using Compound Index for Sorting

Pattern	Index
Filter status, sort by date	{status:1, createdAt:-1}
Range + sort same key	{createdAt:-1}

10. Avoiding Index Intersection

Issue	Solution
Two single indexes intersected	Slower than one compound
Fix	Create compound {a:1, b:1} for common queries
Drop	Remove redundant single-field indexes