TL;DR
Indexes speed up lookups and sorting in the database. Poor or missing indexes lead to slow queries and full table scans. Here’s when to add indexes and how to optimize queries in 2026.
Who this is for
- Developers writing SQL and ORM queries (Prisma, TypeORM)
- Teams caring about backend performance
- Anyone analyzing slow queries in production
Keyword (SEO)
database indexes, query optimization, postgresql, mysql, performance, explain
Why Indexes?
- Faster lookups –
WHERE id = ?,WHERE email = ?instead of scanning the whole table - Faster sorting –
ORDER BY created_atwith an index oncreated_at - Faster JOINs – index on the join column (foreign key)
- Avoid full table scan – on large tables full scan = slow queries
When to Add Indexes?
- Columns in WHERE (e.g.
user_id,status,email) - Columns in ORDER BY (e.g.
created_at DESC) - Columns in JOIN (foreign keys)
- Columns in UNIQUE / PRIMARY KEY (usually auto-indexed)
When to Avoid Too Many Indexes?
- Each index slows INSERT/UPDATE/DELETE (DB must update the index)
- Small tables – full scan is often fast enough
- Columns rarely used in WHERE – index may not pay off
ANALYZE / EXPLAIN – Finding Slow Queries
PostgreSQL:
EXPLAIN ANALYZE
SELECT * FROM orders WHERE user_id = 123 ORDER BY created_at DESC LIMIT 10;
Look for:
- Seq Scan (full table scan) on a large table – often a sign to add an index
- Index Scan / Index Only Scan – index used, usually OK
- cost – higher cost = more expensive query
Prisma: enable log: ['query'] in dev and measure query time; analyze slow queries in the DB with EXPLAIN.
Example: Index on Frequently Filtered Column
-- Table orders, often: WHERE user_id = ? ORDER BY created_at DESC
CREATE INDEX idx_orders_user_created ON orders (user_id, created_at DESC);
A composite index (user_id, created_at) can serve both the filter on user_id and the sort on created_at without a separate sort step.
Checklist / steps
- Enable slow query logging (e.g. PostgreSQL
log_min_duration_statement) - Run EXPLAIN ANALYZE on slow queries
- Add indexes on columns used in WHERE and ORDER BY for those queries
- Avoid indexing every column – balance matters
- After adding an index, measure query time again
FAQ
Is an index on every column a good idea?
No. Too many indexes slow down writes and use space. Add indexes for specific queries (WHERE, ORDER BY, JOIN).
What is a composite index?
An index on multiple columns, e.g. (user_id, created_at). Column order matters – the first column should be the one you filter on most often.
Query still slow after adding an index?
Check that the execution plan (EXPLAIN) actually uses the index. Sometimes the DB chooses full scan (e.g. when returning a large fraction of rows). Narrow the result (LIMIT, better WHERE) or consider table partitioning.