Query Optimization

Definition

Query optimization is how a database returns results with the least work — chiefly by using indexes so it reads only the records it needs, and by letting the query planner pick the cheapest access plan. Fewer records touched means less execution time.

---

Core Ideas

Indexing

• Selectivity is the primary factor: a good index lets the query select only the required records without scanning many extra keys or documents.
• Covered query — when every field in the query and every field returned is in the same index, the DB answers from the index alone (which lives in RAM) without touching the documents.
• Compound index ordering rule — equality → sort → range: put equality-test fields first, then sort fields, then range fields.
• Index properties (MongoDB): unique, sparse (only indexes documents that have the field — smaller, but can’t sort; prefer partial indexes since 3.2), multikey (indexes array elements), background vs foreground builds, plus geospatial (2d, 2dsphere) and $text indexes.

Access plans (relational)

The query processor computes an access path per table and every join order permutation, costs each resulting access plan, and executes the lowest-cost final access plan.

Inspecting & profiling

• explain() returns the plan in three modes: queryPlanner → executionStats → allPlansExecution.
• hint() forces a specific index.
• The database profiler logs slow operations to system.profile (level 0 off, 1 slow-only with slowms, 2 all); inspect with mongotop / mongostat.

---

Relationships

• Databases — parent domain
• NoSQL and Document Databases — indexing and covered queries apply to MongoDB
• Database Transactions — locking and isolation affect plan execution
• Big-O Notation — index lookups turn O(n) scans into O(log n) seeks

---

References

• MongoDB Performance