Indexes are one of those things every developer knows they should use — but fewer developers truly understand how databases use them and why they sometimes don’t work the way you expect.
Ever added an index, shipped it to production, and saw… absolutely no performance improvement? Yeah. Let’s talk about why that happens and how to think about indexes like a database engine does.
1. What an Index Actually Is (Mentally, Not Academically)
At a high level, most indexes are sorted data structures (often B-trees) that let the database avoid scanning the entire table.
Instead of this:
“Check every row to find matches”
The DB can do this:
“Jump directly to the matching range”
Sounds great. But here’s the catch:
Indexes are only useful if the query can actually use that ordering.
2. Why SELECT * Can Break Index Usage
This surprises a lot of people.
SELECT * FROM users WHERE email = 'test@example.com';
Even if email is indexed, the database may still:
- Use the index to find the row
- Jump back to the table to fetch all columns
That second step is called a heap lookup, and it’s expensive.
Why this matters
If your query:
- Returns many rows
- Or fetches many columns
…the cost of table lookups can dominate.
Better approach
SELECT id, email FROM users WHERE email = 'test@example.com';
Smaller result set = fewer reads = faster query.
3. Index Order Matters More Than You Think
Consider this index:
CREATE INDEX idx_orders_user_date ON orders (user_id, created_at);
This index is great for:
WHERE user_id = 42
AND created_at >= '2025-01-01';
But not great for:
WHERE created_at >= '2025-01-01';
Why?
Indexes are sorted left to right.
The database can only efficiently use the index starting from the first column (user_id). Skip it, and the index becomes mostly useless.
Rule of thumb
Put:
- Highly selective columns first
- Frequently filtered columns before sorting columns
4. Functions in WHERE Clauses Kill Indexes
This is a classic footgun.
-- ❌ Index will not be used
WHERE DATE(created_at) = '2025-02-01';
Even if created_at is indexed, the database must:
- Apply the function to every row
- Then compare results
Fix it by flipping the logic
-- ✅ Index-friendly
WHERE created_at >= '2025-02-01'
AND created_at < '2025-02-02';
Same result. Completely different performance.
5. Low-Cardinality Indexes Often Don’t Help
Indexing a column with very few unique values is usually pointless.
Examples:
is_active-
statuswith 3 values deleted = false
Why?
- The DB still has to scan a large portion of the table
- The index doesn’t reduce the search space much
When it can make sense
- Combined with other columns
- On very large tables
- With partial indexes
CREATE INDEX idx_active_users
ON users (id)
WHERE is_active = true;
Now that index earns its keep.
6. Indexes Speed Reads — and Slow Writes
Every index must be updated on:
- INSERT
- UPDATE
- DELETE
Too many indexes can:
- Slow down writes
- Increase lock contention
- Bloat memory and disk usage
The mistake
“Query is slow? Add an index.”
The better question
“Is this query hot and unavoidable?”
Indexes are trade-offs, not free upgrades.
7. Execution Plans Are Your Best Friend
If you’re not using EXPLAIN, you’re guessing.
EXPLAIN ANALYZE
SELECT id FROM users WHERE email = 'test@example.com';
This tells you:
- Was the index used?
- How many rows were scanned?
- Where the time actually went?
Reality check
If the planner chooses a sequential scan, it’s usually for a reason.
Your job is to understand why, not to fight it blindly.
8. Covering Indexes: The Secret Weapon
A covering index includes all columns needed by the query.
CREATE INDEX idx_users_email_id
ON users (email, id);
Now this query:
SELECT id FROM users WHERE email = 'test@example.com';
Can be answered entirely from the index — no table lookup at all.
This is one of the biggest performance wins you can get for read-heavy workloads.
Key Takeaway
Indexes don’t make queries fast by default.
They make specific access patterns fast.
To use them well, you need to think like the database:
- How is the data sorted?
- How many rows are touched?
- Are we forcing extra work?
- Is the index actually selective?
The best-performing systems don’t have the most indexes — they have the right ones.
Discussion
What’s the most misleading index you’ve ever added — the one you were sure would help, but didn’t move the needle at all?
Top comments (0)