Indexes
Speed up queries with indexes: B-tree, unique, composite, and when to avoid them.
What you'll learn
- What an index is and how it speeds up queries
- B-tree indexes — the default and most common type
- Composite indexes and column ordering
- Unique indexes vs unique constraints
- The trade-off: faster reads vs slower writes and storage
Concept
What Is an Index?
An index is a separate data structure that the database maintains alongside your table to find rows quickly. Without an index, the database must scan every row (a sequential scan) to find matches. With an index, it looks up the value in the index (typically O(log n) for a B-tree) and jumps directly to the matching rows. The difference on a million-row table can be milliseconds vs seconds.
B-Tree Indexes
The default index type in most databases is a B-tree (balanced tree). It keeps values sorted, so it accelerates equality lookups (WHERE id = 42), range queries (WHERE price BETWEEN 10 AND 50), and ORDER BY on the indexed column. PostgreSQL, MySQL, and SQLite all create B-tree indexes by default.
Composite Indexes
A composite index covers multiple columns: CREATE INDEX idx ON orders (user_id, created_at). It speeds up queries that filter on both columns in order, or on just user_id (the leftmost prefix). It does not help queries that filter only on created_at — column order matters. Put the most selective column first, and the column used in equality (vs range) conditions first.
Unique Indexes
A CREATE UNIQUE INDEX enforces uniqueness just like a UNIQUE constraint. In fact, UNIQUE and PRIMARY KEY constraints are implemented as unique indexes under the hood. You can create a unique index directly when you need uniqueness on a column that is not part of a constraint declaration.
The Trade-Off
Indexes are not free. Every INSERT, UPDATE, and DELETE must update every index on the table, which slows down writes. Indexes also consume disk space. A good rule of thumb: index columns that are frequently used in WHERE, JOIN, and ORDER BY clauses, and avoid indexing columns that are rarely queried or that are updated very frequently. Use EXPLAIN to verify that an index is actually being used.
Example
-- Create a B-tree index on a frequently filtered column
CREATE INDEX idx_orders_status ON orders (status);
-- Composite index: user_id first (equality), then created_at (range/sort)
CREATE INDEX idx_orders_user_date
ON orders (user_id, created_at);
-- Unique index to enforce uniqueness on a column
CREATE UNIQUE INDEX idx_users_email ON users (email);
-- Partial index: only index active users (PostgreSQL)
CREATE INDEX idx_active_users ON users (last_login)
WHERE active = TRUE;
-- Check if the index is used
EXPLAIN SELECT * FROM orders
WHERE user_id = 42
ORDER BY created_at DESC;
Five index patterns: single-column, composite with careful ordering, unique, partial (PostgreSQL), and EXPLAIN to verify usage.
Try it
- SQL Formatter
Format CREATE INDEX and EXPLAIN statements.
- Code Diff
Compare query plans before and after adding an index.
Common mistakes
The mistake
Adding too many indexes
The fix
Each index slows down writes and uses storage. Index only columns that are frequently queried. Use EXPLAIN to confirm an index is used before keeping it.
The mistake
Wrong column order in a composite index
The fix
Put equality-filtered columns before range-filtered columns. A composite index on (a, b) helps WHERE a = 1 AND b > 5, but not WHERE b > 5 alone.
Practice Indexes
5 exercises
Look up unfamiliar terms
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
Build real projects
Apply what you learned by building guided projects with starter code and solutions.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.