How do indexes make your database queries faster, and what's the catch?

Database indexes are data structures (typically B-trees or hash tables) that provide faster data retrieval at the cost of additional storage and slower writes.

Without an index, the database performs a full table scan—examining every row to find matches. An index creates a sorted structure pointing to row locations, allowing logarithmic-time lookups instead of linear scans.

B-tree indexes (the most common type) maintain sorted data and support equality and range queries efficiently. They're ideal for columns used in WHERE, JOIN, and ORDER BY clauses. Hash indexes only support equality comparisons but are extremely fast for exact matches.

Composite indexes cover multiple columns and are powerful when you query those columns together. The order matters: an index on (a, b, c) can help queries filtering on (a), (a, b), or (a, b, c), but not just (b) or (c).

Trade-offs to consider: every index slows down INSERT, UPDATE, and DELETE operations because the index must be maintained. Indexes consume disk space. Too many indexes can actually slow down the query optimizer as it chooses between them.

Best practices: index foreign keys used in JOINs, analyze slow query logs to identify missing indexes, use EXPLAIN/EXPLAIN ANALYZE to understand query plans, consider covering indexes that include all columns a query needs, and regularly monitor index usage—unused indexes should be removed.

Understanding your query patterns is essential. Index the columns you query, not every column, and measure the actual impact on your workload.