Database Indexing
DatabasesA database index is a data structure that improves the speed of data retrieval operations on a table at the cost of additional storage space and slower writes, functioning like a book's index for quick lookups.
Without an index, the database must scan every row in a table to find matching records (a full table scan). An index creates a separate data structure — typically a B-tree or B+ tree — that maps column values to their row locations, enabling logarithmic-time lookups instead of linear scans.
Common index types include B-tree indexes (the default, good for range queries and equality), hash indexes (faster for exact matches, no range support), composite indexes (on multiple columns), full-text indexes (for text search), and spatial indexes (for geographic data).
Indexes have trade-offs: they speed up reads but slow down writes (every insert/update must also update the index). Over-indexing wastes storage and degrades write performance. Under-indexing causes slow queries.
In system design, choosing the right indexes is critical for query performance. Always index columns used in WHERE clauses, JOIN conditions, and ORDER BY statements. Use EXPLAIN/ANALYZE to verify queries use indexes effectively.
Related Terms
Ready to design?
Practice using database indexing in a real system design on Supaboard's interactive whiteboard.
Browse Challenges