How do indexes help, and what do they cost?

Definition:

An index is an extra data structure that helps the database find matching records without inspecting every document. Reads can become much faster, but inserts, updates and deletes must maintain the relevant indexes. Indexes are most useful when their fields and order match important query patterns.

Key points:

• Indexes reduce the documents examined for matching queries.

• Each index takes storage and adds work to writes.

• Check real query patterns before adding indexes.

• Measure keys examined, documents examined and returned documents together; merely seeing an index in a plan does not prove efficiency.

Interview point:

• Include write cost and storage in the trade-off, and show which actual query the index is meant to improve.