Indexes are data structures that speed up data retrieval at the cost of additional storage and slower writes. Choose indexes based on query patterns.
B-tree indexes (default in most databases) are optimal for range queries and equality comparisons. Hash indexes are faster for exact matches but don't support ranges.
Compound indexes cover multiple fields: index on {lastName, firstName} supports queries filtering by lastName alone or by both fields. Field order matters — put most selective field first.
Covering indexes include all fields needed by a query, eliminating the need to read the actual document.
Partial indexes index only documents matching a condition, reducing index size. Unique indexes enforce uniqueness constraints.
Use EXPLAIN (PostgreSQL) or explain() (MongoDB) to analyze query plans. Common mistakes: over-indexing (slows writes), missing indexes on foreign keys, and indexes unused by actual queries.