Database Indexing
Creating data structures that speed up data retrieval operations on database tables by allowing the database to find rows without scanning the entire table.
Detailed Explanation
Database indexes are like a book's index—they let you jump directly to the data you need instead of reading every page. An index on a column stores a sorted copy of that column with pointers to the full rows. Queries that filter or sort by indexed columns are dramatically faster.
Common index types: B-tree (default, good for equality and range queries), hash (fast equality only), GIN (for arrays and full-text search), and GiST (for geometric and geographic data). Over-indexing slows writes, so balance read performance with write overhead.
Why It Matters
Proper indexing can improve query performance by orders of magnitude. Poor indexing is the most common cause of database performance problems.
Real-World Example
A query on a users table with 10 million rows: without index on email, the query scans all 10 million rows (2 seconds). With an index, it finds the user in milliseconds.
When to Use
Index columns used in WHERE clauses, JOIN conditions, and ORDER BY. Avoid indexing columns with low selectivity (like boolean fields) or columns that are frequently updated.
Advantages
- Dramatically faster query performance
- Reduces database load
- Improves application response times
- Automatic in most databases (primary key index)
- Can be created on multiple columns (composite index)
Disadvantages
- Slows down INSERT, UPDATE, DELETE operations
- Consumes disk space
- Over-indexing degrades write performance
- Requires maintenance as data grows
- Wrong indexes can be worse than no indexes
Related Terms
Frequently Asked Questions
When should I add an index?
Index columns used in WHERE, JOIN, and ORDER BY. If a query scans too many rows or is slow, check if an appropriate index exists. Use EXPLAIN to analyze query plans.
What is a composite index?
A composite index covers multiple columns. The order matters—index (last_name, first_name) supports queries filtering by last_name alone or by both, but not by first_name alone.
How do I know which indexes to add?
Use EXPLAIN (or EXPLAIN ANALYZE) to see query plans. Look for sequential scans on large tables. Add indexes on columns used in JOIN, WHERE, and ORDER BY. Remove unused indexes.
Can I over-index a table?
Yes. Every index slows writes and consumes disk space. Indexes that are never used waste resources. Regularly review and remove unused indexes.
What is a covering index?
A covering index includes all columns needed by a query. The database can answer the query entirely from the index without reading the table rows. This provides the maximum performance benefit.