Database Indexing Explained for Web Developers
Your web app feels snappy during development, but as data grows, queries that once returned in milliseconds now take seconds. Users complain, and your database CPU spikes. The culprit is often missing or misused indexes. Indexing is one of the highest-leverage skills for backend developers, yet it's frequently misunderstood. This guide explains how database indexes work, when to use them, and how to avoid common pitfalls.
What Is a Database Index?
Think of an index like the index at the back of a textbook. Instead of scanning every page to find a topic, you look it up in the index, which points you to the right pages. A database index works similarly: it's a data structure that allows the database engine to find rows quickly without scanning the entire table.
Without an index, a query like SELECT * FROM users WHERE email = 'alice@example.com' forces a full table scan — the database reads every row until it finds a match. With an index on email, the database can jump directly to the matching row.
How Indexes Work Under the Hood
Most relational databases use B-tree (balanced tree) indexes by default. A B-tree keeps data sorted and allows searches, sequential access, insertions, and deletions in logarithmic time. This is why indexed lookups are fast even for millions of rows.
Other index types include:
- Hash indexes: Good for exact-match lookups, not range queries.
- Bitmap indexes: Efficient for low-cardinality columns (e.g., status flags), common in data warehousing.
- Full-text indexes: Specialized for searching text content.
- GiST/GIN indexes: Used in PostgreSQL for geometric and JSON data.
For most web applications, B-tree indexes are the workhorse.
When to Create an Index
Indexes are not free — they consume storage and slow down writes. Create them strategically:
- Columns in WHERE clauses: If you frequently filter by a column, index it.
- Columns in JOIN conditions: Index foreign keys to speed up joins.
- Columns in ORDER BY: An index can eliminate the need for a sort operation.
- Columns in GROUP BY: Indexes can help aggregate queries.
However, avoid indexing columns that are rarely queried or have very low cardinality (e.g., a boolean flag) unless used in combination with other columns.
Types of Indexes and Their Use Cases
| Index Type | Best For | Example |
|---|---|---|
| Single-column | Simple filters | CREATE INDEX idx_email ON users(email); |
| Composite | Queries filtering on multiple columns | CREATE INDEX idx_name_age ON users(last_name, first_name); |
| Unique | Enforcing uniqueness | CREATE UNIQUE INDEX idx_username ON users(username); |
| Partial | Indexing a subset of rows | CREATE INDEX idx_active ON users(email) WHERE active = true; |
| Covering | Queries that only need indexed columns | CREATE INDEX idx_covering ON users(email, name); |
How to Create and Verify Indexes
Creating an index is straightforward. For example, in PostgreSQL:
CREATE INDEX idx_users_email ON users(email);After creating an index, verify that it's being used. Use EXPLAIN (or EXPLAIN ANALYZE) to see the query plan:
EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'alice@example.com';Look for "Index Scan" or "Index Only Scan" instead of "Seq Scan". If you see a sequential scan, the index might not be used due to type mismatches, functions on the column, or outdated statistics.
Common Indexing Mistakes
- Indexing everything: Too many indexes slow down writes and waste space.
- Ignoring composite index order: For a composite index on
(a, b), queries that filter onbalone cannot use the index efficiently. - Using functions on indexed columns:
WHERE YEAR(created_at) = 2025prevents index usage. Instead, use range conditions:WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01'. - Not updating statistics: Databases rely on statistics to choose indexes. Run
ANALYZEregularly. - Overlooking write overhead: Every INSERT, UPDATE, and DELETE must update indexes. For write-heavy tables, be selective.
Advanced Techniques
Covering Indexes
A covering index includes all columns needed by a query, so the database can retrieve data directly from the index without touching the table. This can dramatically speed up read-heavy queries.
Partial Indexes
If you frequently query a subset of rows (e.g., active users), a partial index is smaller and faster than a full index.
Index-Only Scans
Some databases support index-only scans, where all required data is in the index. This is the fastest type of index access.
Monitoring and Maintaining Indexes
Indexes can become bloated over time due to updates and deletes. In PostgreSQL, VACUUM and REINDEX help maintain performance. In MySQL, OPTIMIZE TABLE can rebuild indexes. Regularly review slow query logs to identify missing indexes.
FAQ
How do I know if my query is using an index?
Use the EXPLAIN command (or EXPLAIN ANALYZE) before your query. The output shows whether the database uses an index scan or a sequential scan.
Can I have too many indexes?
Yes. Each index adds overhead to write operations and consumes storage. Aim for a balance based on your read/write ratio.
What is the difference between a clustered and non-clustered index?
A clustered index determines the physical order of rows in the table (like a primary key in InnoDB). A non-clustered index is a separate structure that points to the rows. A table can have only one clustered index but many non-clustered ones.
Ready to optimize your database? Start by analyzing your slow queries and adding indexes where needed. For quick JSON formatting and validation, try our JSON Formatter — it's free and runs entirely in your browser.