Explain Database Index Benefits and Write Costs

Why do relational databases use indexes, and why is it generally a poor design to create one on every column? Explain how an index affects read behavior, write behavior, storage use, and query-planner decisions. Give examples of a query that would probably benefit from an index and a column that might not.

Clarifying Questions to Ask

• Which database engine and index family should be assumed for the discussion?
• Is the workload mainly transactional, analytical, or a mix?
• Are composite indexes and covering indexes part of the scope?

What a Strong Answer Covers

• How an index allows selective lookups or ordered access to avoid scanning every row.
• The write amplification, storage, cache, and maintenance costs that each added index brings.
• Selectivity, access patterns, the order of composite keys, and whether an index can cover a query.
• Cases where the optimizer may prefer a table scan even when an index is available.
• A measurement-driven approach using query plans and production-shaped workloads.

Follow-up Questions

• When would a composite index on (tenant_id, created_at) be more useful than two separate single-column indexes?
• Why is an index on a low-cardinality boolean column often ineffective?
• How do index-only scans and clustered storage alter the trade-off?

Overview: Review why database indexes speed up selective reads and why indexing every column can hurt writes, storage, and cache efficiency. The answer ties selectivity, composite key order, covering indexes, and query plans to a practical measurement-driven indexing strategy.