How do database indexes improve query performance, and what are the trade-offs?

Question

Accepted Answer

Database indexes are data structures that improve the speed of data retrieval operations. They work similarly to an index in a book, allowing the database to find specific rows without scanning the entire table.

Without an index, the database performs a full table scan, examining every row to find matches. For a table with one million rows, this means reading one million rows even if only one matches. With an appropriate index, the database can locate the matching rows directly, often examining only a handful of entries.

Most databases use B-tree indexes by default. These maintain data in sorted order, enabling efficient lookups, range queries, and ordering operations. A query filtering on an indexed column can find results in logarithmic time rather than linear time.

Indexes improve performance for several operations. SELECT queries with WHERE clauses benefit when the filtered column is indexed. JOIN operations are faster when join columns are indexed. ORDER BY and GROUP BY operations benefit from indexes on the sorted or grouped columns.

However, indexes come with trade-offs.

Storage overhead is one cost. Each index requires disk space to store its data structure. Tables with many indexes consume significantly more storage.

Write performance suffers because every INSERT, UPDATE, or DELETE must also update all relevant indexes. A table with ten indexes requires eleven write operations for each row change instead of one.

Index maintenance adds overhead. Indexes can become fragmented over time, requiring periodic rebuilding to maintain optimal performance.

Best practices include indexing columns frequently used in WHERE clauses and JOIN conditions. Avoid indexing columns with low cardinality, meaning few distinct values, like boolean fields. Use composite indexes for queries that filter on multiple columns together. Monitor query execution plans to identify missing indexes. Remove unused indexes that only add write overhead.

The key is finding the right balance. Too few indexes slow reads. Too many slow writes and waste storage.