Your query takes 5 seconds. You add an index. Now it takes 5 milliseconds. That's not an exaggeration — that's literally what happens when a query stops doing a full table scan on a million rows and starts using an index instead.
Indexing is one of those things that feels optional until your app slows to a crawl at 3 AM and your users are refreshing in frustration. This guide covers how indexes actually work under the hood, which types exist, when to add them, and — just as important — when NOT to.
What Is a Database Index?
Think of a textbook. If you want to find every mention of "B-tree," you have two choices: read every single page (full table scan), or flip to the index at the back and get the exact page numbers (index lookup). A database index works the same way.
An index is a separate data structure that the database maintains alongside your table. It stores a sorted copy of one or more columns along with pointers back to the actual rows. When you query with a WHERE clause on an indexed column, the database checks the index first to find matching row locations, then fetches only those rows.
Without an index, the database has no choice but to examine every single row in the table. On a table with 10 million rows, that's painful. With an index, it can jump straight to the matching rows in O(log n) time.
How B-Tree Indexes Work
B-tree (balanced tree) is the default index type in PostgreSQL, MySQL, SQLite, and most relational databases. Here's the simplified structure:
[50] ← Root node
/ \
[20, 35] [70, 85] ← Branch nodes
/ | \ / | \
[5,10] [22,30] [37,45] [55,65] [72,80] [90,95] ← Leaf nodes (point to actual rows)
Query: WHERE age = 37
Step 1: Start at root → 37 < 50 → go left
Step 2: At [20, 35] → 37 > 35 → go right child
Step 3: At leaf [37, 45] → found 37! → follow pointer to row
Result: 3 comparisons instead of scanning all rowsThe key insight: B-trees are balanced, meaning every path from root to leaf is the same length. For a table with 10 million rows, a B-tree index typically has a depth of 3-4 levels. That means finding any value requires only 3-4 disk reads — no matter how big the table gets.
Leaf nodes are also linked together in order, which makes range queries (BETWEEN, greater than, less than) efficient. The database finds the starting leaf node and then walks through the linked leaves sequentially.
Types of Database Indexes
Different query patterns call for different index types. Here are the ones you'll actually encounter in production:
B-Tree Index (Default)
Supports equality (=) and range queries (>, <, BETWEEN, LIKE 'prefix%'). This is your go-to index for 95% of use cases. Created by default when you run CREATE INDEX.
Hash Index
Only supports exact equality (=). Slightly faster than B-tree for equality lookups but useless for range queries. PostgreSQL supports them but they're rarely the right choice. MySQL InnoDB doesn't support standalone hash indexes.
Full-Text Index
Designed for searching text content — supports word matching, stemming, ranking. Use for search features instead of LIKE '%keyword%' which can't use regular indexes. Both PostgreSQL (GIN/GiST) and MySQL (FULLTEXT) have built-in support.
Composite (Multi-Column) Index
Covers multiple columns in a single index. Column order matters — index on (country, city) supports queries on country alone OR country + city, but NOT city alone. Put the most frequently filtered and most selective column first.
Partial Index (Filtered Index)
Only indexes rows matching a condition. Example: CREATE INDEX idx_active_users ON users(email) WHERE active = true. Smaller index, faster lookups, less storage. Available in PostgreSQL and SQL Server.
Unique Index
Enforces uniqueness on the indexed columns — rejects duplicate values. Automatically created when you add a UNIQUE constraint. Also acts as a performance index since the database knows there's at most one match.
When to Add an Index
Not every column needs an index. Here's a decision framework:
| Scenario | Index? | Why |
|---|---|---|
| Read-heavy table (90%+ reads) | Yes | Reads benefit massively, write overhead is minimal relative to read gains |
| Columns in WHERE clauses | Yes | Direct filter columns are the primary use case for indexes |
| JOIN columns (foreign keys) | Yes | JOINs without indexes cause nested loop scans — O(n²) behavior |
| ORDER BY columns | Yes | Index provides pre-sorted data, avoids expensive filesort operations |
| GROUP BY columns | Yes | Allows the database to group without sorting first |
| High-cardinality columns | Yes | Many unique values = index narrows results efficiently |
| Columns in DISTINCT queries | Maybe | Helps if column has high cardinality, less helpful otherwise |
When NOT to Add an Index
Indexes aren't free. Each one costs storage, slows down writes, and adds maintenance overhead. Here's when to skip them:
Small tables (under 1,000 rows)
A full table scan on 1,000 rows is already fast. The overhead of maintaining an index isn't worth it. The database might ignore the index entirely and scan the table anyway.
Write-heavy tables with few reads
If a table receives thousands of inserts per second but is rarely queried (like a logging table), indexes add write latency without much read benefit. Consider indexing only when you need to query it.
Low-cardinality columns
A column with only 2-3 unique values (boolean, status: active/inactive) doesn't benefit from a B-tree index. The database would still need to read a large portion of the table. Exception: partial indexes on the rare value.
Columns that are frequently updated
If a column changes on nearly every write, the index must be updated on every write too. This adds significant overhead. Only index it if the read benefit clearly outweighs the write cost.
Tables that are rebuilt frequently
If you TRUNCATE and reload the table regularly (staging data, temp tables), indexes slow down the bulk load. Drop indexes before bulk insert, recreate after.
Creating Indexes — PostgreSQL & MySQL Examples
Let's look at actual SQL for creating and analyzing indexes:
PostgreSQL
-- Basic B-tree index
CREATE INDEX idx_users_email ON users(email);
-- Composite index (column order matters!)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);
-- Partial index (only active users)
CREATE INDEX idx_active_users_email ON users(email)
WHERE active = true;
-- Unique index
CREATE UNIQUE INDEX idx_users_username ON users(username);
-- Full-text search index (GIN)
CREATE INDEX idx_posts_search ON posts
USING gin(to_tsvector('english', title || ' ' || body));
-- Hash index (equality only)
CREATE INDEX idx_sessions_token ON sessions USING hash(token);
-- Check index size
SELECT pg_size_pretty(pg_relation_size('idx_users_email'));
-- List all indexes on a table
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';MySQL
-- Basic index CREATE INDEX idx_users_email ON users(email); -- Composite index CREATE INDEX idx_orders_user_date ON orders(user_id, created_at); -- Unique index CREATE UNIQUE INDEX idx_users_username ON users(username); -- Full-text index ALTER TABLE posts ADD FULLTEXT INDEX idx_posts_ft(title, body); -- Prefix index (index only first N characters — saves space) CREATE INDEX idx_users_email_prefix ON users(email(50)); -- Show indexes on table SHOW INDEX FROM users; -- Check index usage stats (MySQL 8.0+) SELECT * FROM sys.schema_index_statistics WHERE table_name = 'users';
EXPLAIN ANALYZE — Verifying Index Usage
-- PostgreSQL: EXPLAIN ANALYZE shows actual execution EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'john@example.com'; -- Good output (using index): -- Index Scan using idx_users_email on users -- Index Cond: (email = 'john@example.com') -- Planning Time: 0.1 ms -- Execution Time: 0.05 ms ← Fast! -- Bad output (not using index): -- Seq Scan on users -- Filter: (email = 'john@example.com') -- Rows Removed by Filter: 999999 -- Planning Time: 0.1 ms -- Execution Time: 4800 ms ← Slow! -- MySQL: EXPLAIN shows query plan EXPLAIN SELECT * FROM users WHERE email = 'john@example.com'; -- Look for: type=ref or type=const (good) vs type=ALL (bad)
Real-World Indexing Scenarios
Let's look at common queries you'd encounter in a typical SaaS application and how to index them properly:
Scenario: User login lookup
Query: SELECT * FROM users WHERE email = ? AND active = true
Best index: CREATE INDEX idx_users_login ON users(email) WHERE active = true
Why: Partial index covers the exact query pattern. Smaller than indexing all users (including deactivated accounts). Exact match on email is highly selective.
Scenario: Order history with pagination
Query: SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC LIMIT 20
Best index: CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC)
Why: Composite index matches both the WHERE and ORDER BY. The database can satisfy this query using an Index Only Scan (if you include needed columns) without sorting — it reads the pre-sorted index in order.
Scenario: Dashboard aggregation query
Query: SELECT status, COUNT(*) FROM orders WHERE created_at > NOW() - INTERVAL '30 days' GROUP BY status
Best index: CREATE INDEX idx_orders_recent ON orders(created_at, status)
Why: The range filter on created_at comes first (leftmost prefix rule). Including status in the index allows an Index Only Scan for the GROUP BY without touching the heap.
Scenario: Full-text product search
Query: SELECT * FROM products WHERE to_tsvector('english', name || ' ' || description) @@ to_tsquery('wireless & headphones')
Best index: CREATE INDEX idx_products_search ON products USING gin(to_tsvector('english', name || ' ' || description))
Why: GIN index enables fast full-text search. Without it, PostgreSQL must compute to_tsvector on every row for every search query — impossibly slow on large product catalogs.
Scenario: Finding unprocessed jobs
Query: SELECT * FROM jobs WHERE status = 'pending' ORDER BY priority DESC, created_at ASC LIMIT 10
Best index: CREATE INDEX idx_jobs_pending ON jobs(priority DESC, created_at ASC) WHERE status = 'pending'
Why: Partial index only covers pending jobs (likely a small fraction of all jobs). Index order matches the ORDER BY exactly, so no sort is needed. As jobs complete, they leave the index automatically.
Index Maintenance & Monitoring
Creating indexes is just the beginning. You need to monitor them over time to ensure they stay efficient and relevant:
-- PostgreSQL: Find unused indexes (candidates for removal)
SELECT schemaname, relname AS table, indexrelname AS index,
idx_scan AS times_used, pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0 AND indexrelname NOT LIKE '%pkey%'
ORDER BY pg_relation_size(indexrelid) DESC;
-- PostgreSQL: Find bloated indexes (need REINDEX)
SELECT schemaname, tablename, indexname,
pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexname::regclass) DESC
LIMIT 20;
-- PostgreSQL: Rebuild a bloated index without locking
REINDEX INDEX CONCURRENTLY idx_users_email;
-- MySQL: Check index cardinality (low = possibly useless)
SELECT TABLE_NAME, INDEX_NAME, COLUMN_NAME, CARDINALITY
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY CARDINALITY ASC;A good rule of thumb: review your indexes monthly. Drop any that haven't been used in 30+ days (check pg_stat_user_indexes in PostgreSQL). Each unused index is wasting disk space and slowing down every write operation for zero benefit.
Index Type Comparison
| Type | Best For | Supports Range? | Storage | DB Support |
|---|---|---|---|---|
| B-Tree | General purpose, range queries | Yes | Medium | All databases |
| Hash | Exact equality lookups | No | Small | PostgreSQL, Memory engines |
| Full-Text | Text search, word matching | N/A | Large | PostgreSQL (GIN), MySQL (FULLTEXT) |
| Composite | Multi-column filters | Yes (leftmost) | Medium-Large | All databases |
| Partial | Subset of rows (filtered) | Yes | Small | PostgreSQL, SQL Server |
| Unique | Enforcing uniqueness + lookups | Yes | Medium | All databases |
| GiST | Geometric/spatial data | Yes | Large | PostgreSQL |
Measuring Index Performance Impact
Adding an index is an optimization hypothesis. You need to verify it actually helps. Here's a systematic approach to measuring index impact:
-- Step 1: Benchmark BEFORE the index
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 12345 AND status = 'completed';
-- Note: Execution time, rows scanned, buffer hits
-- Step 2: Create the index
CREATE INDEX CONCURRENTLY idx_orders_user_status ON orders(user_id, status);
-- Step 3: Benchmark AFTER (run same query)
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 12345 AND status = 'completed';
-- Compare: Execution time should be dramatically lower
-- Step 4: Check the overhead cost
-- Storage used by the new index:
SELECT pg_size_pretty(pg_relation_size('idx_orders_user_status'));
-- Write overhead: benchmark INSERT performance before/after
-- (use pgbench or your load testing tool)A well-chosen index should show 10x-1000x improvement in query time. If the improvement is less than 2x, the index might not be worth the write overhead. Re-evaluate whether the query pattern justifies maintaining the index long-term.
Common Indexing Mistakes
❌ Indexing every column
More indexes = slower writes, more storage, more maintenance. Each INSERT now updates 10 indexes instead of 2. Only index columns that appear in WHERE, JOIN, ORDER BY, or GROUP BY of actual queries you run.
❌ Wrong column order in composite indexes
An index on (city, country) does NOT help a query filtering only by country. The leftmost prefix rule means columns must be used left-to-right. Put the most commonly filtered column first, then the most selective.
❌ Using functions on indexed columns
WHERE LOWER(email) = 'test@example.com' won't use an index on email. The function prevents index usage. Fix: create a functional index (CREATE INDEX idx ON users(LOWER(email))) or normalize data on insert.
❌ Not checking if indexes are actually used
You create an index but never run EXPLAIN to verify the query planner uses it. Sometimes the optimizer chooses a sequential scan anyway (small table, low selectivity). Always verify with EXPLAIN ANALYZE after creating indexes.
❌ Duplicate and redundant indexes
Having an index on (user_id) AND a composite on (user_id, created_at) means the single-column index is redundant — the composite already covers user_id-only queries. Remove duplicates to save space and write overhead.
❌ Ignoring index bloat
After many UPDATEs and DELETEs, indexes accumulate dead entries (especially in PostgreSQL). This bloats the index size and slows lookups. Run REINDEX periodically on heavily updated tables, or use pg_repack for zero-downtime reindex.
Indexing Best Practices
1. Start with EXPLAIN ANALYZE, not guesses
Don't guess which indexes you need. Run your actual slow queries with EXPLAIN ANALYZE. Look for Seq Scans on large tables — those are your indexing candidates. Data-driven decisions beat intuition.
2. Index foreign keys
Every foreign key column should have an index. Without it, JOINs become nested loop scans and DELETE on parent tables requires full scans of child tables to check references.
3. Use composite indexes for multi-column queries
If you frequently query WHERE user_id = ? AND status = ?, a composite index on (user_id, status) is better than two separate indexes. One index lookup beats two index merges.
4. Consider covering indexes for read-heavy queries
If your query only needs columns that are in the index, it never touches the table (Index Only Scan). Include frequently selected columns with INCLUDE (PostgreSQL) to avoid heap fetches.
5. Use partial indexes for common filters
If 95% of your queries filter WHERE status = 'active', create a partial index on that condition. It's smaller, faster, and uses less storage than indexing all rows.
6. Monitor index usage over time
In PostgreSQL, check pg_stat_user_indexes to find unused indexes. In MySQL, check sys.schema_unused_indexes. Drop indexes that aren't being used — they're just slowing down writes for nothing.
7. Create indexes concurrently in production
CREATE INDEX locks the table for writes. Use CREATE INDEX CONCURRENTLY (PostgreSQL) or set lock_timeout to avoid blocking production traffic during index creation.
8. Test with production-like data volumes
An index on a dev database with 100 rows tells you nothing. The query planner makes different decisions based on table statistics. Test indexing strategies against datasets similar in size to production.
Frequently Asked Questions
What is a database index?
A database index is a separate data structure (usually a B-tree) that stores a sorted reference to rows based on specific columns. It allows the database to find rows without scanning the entire table — like a book index that points you to the right page instead of reading every page.
How much faster do indexes make queries?
Indexes can make queries 100x to 10,000x faster depending on table size. A query scanning 10 million rows might take 5 seconds without an index but only 5 milliseconds with one. The improvement is most dramatic on large tables with selective WHERE clauses.
Do indexes slow down writes?
Yes. Every INSERT, UPDATE, or DELETE must also update all relevant indexes. Each index adds overhead — typically 10-30% slower writes per index. For write-heavy tables, having too many indexes can significantly degrade performance.
What is a composite index?
A composite (multi-column) index covers two or more columns. The column order matters — an index on (country, city) helps queries filtering by country alone or by country + city, but NOT queries filtering only by city. Put the most selective column first.
When should I NOT add an index?
Don't index small tables (under 1,000 rows), columns with low cardinality (like boolean or status fields with few unique values), tables that are extremely write-heavy with few reads, or columns rarely used in WHERE/JOIN/ORDER BY clauses.
What is the difference between B-tree and Hash indexes?
B-tree indexes support equality (=) and range queries (greater than, less than, BETWEEN, LIKE 'prefix%'). Hash indexes only support exact equality (=) but are slightly faster for those lookups. B-tree is the default and correct choice 95% of the time.
How do I know if my query is using an index?
Use EXPLAIN or EXPLAIN ANALYZE before your query. Look for 'Index Scan' or 'Index Only Scan' in the output (good). If you see 'Seq Scan' on a large table, your query isn't using an index and may need one.
What is a partial index?
A partial index only indexes rows that match a WHERE condition. For example, indexing only active users (WHERE active = true) creates a smaller, faster index. Great when you only query a subset of the data.
Related Articles & Tools
Conclusion
Database indexing is the single biggest performance lever most developers underutilize. A query that takes 5 seconds on a million-row table can drop to 5 milliseconds with the right index. But indexes aren't magic — they trade write performance for read performance, and the wrong indexes can hurt more than they help.
Start with EXPLAIN ANALYZE on your slowest queries. Index columns that appear in WHERE, JOIN, and ORDER BY. Use composite indexes for multi-column filters. Monitor for unused indexes and kill them. And always, always test with production-scale data — an index that looks great on 100 rows might get ignored by the query planner on 10 million.
Get your indexing right and your database becomes dramatically faster without changing a single line of application code. That's a pretty good trade.