Query Optimization: Stop Scanning Every Row

Your query is slow. You check: the database is fine. Connections are fine. The query is just... slow.

You look at the query:

SELECT * FROM orders WHERE user_id = 123

Seems simple. Why is it slow?

Because there's no index on user_id. The database scans every row to find matching ones.

With a million rows, that's a million comparisons.

Add an index. Instant 100x speedup.

The Index

An index is a lookup table.

Without index:

SELECT * FROM orders WHERE user_id = 123
→ Database scans all 1M rows
→ Finds matches
→ Returns results

With index:

SELECT * FROM orders WHERE user_id = 123
→ Index lookup: user_id=123 → row IDs [5, 12, 99, ...]
→ Fetch those rows
→ Returns results

Index is pre-sorted. Lookup is instant.

What To Index

Index columns that are:

1. Frequently used in WHERE clauses
2. Used in JOIN conditions
3. Used in ORDER BY
4. Used in GROUP BY

Common indexes:

• User lookups: CREATE INDEX on users(id)
• Filter by status: CREATE INDEX on orders(status)
• Date range queries: CREATE INDEX on events(created_at)
• Foreign keys: CREATE INDEX on orders(user_id)

Don't index everything. Each index takes space and slows down writes.

The Cost

Adding an index:

• Takes time (scan table, build index)
• Takes space (duplicate data)
• Slows down inserts/updates (must update index too)
• Speeds up reads massively

Trade-off: slower writes, much faster reads.

For most apps: worth it.

Real Example

I had a query that took 5 seconds:

SELECT * FROM users WHERE email = '[e-mail]'

Added index:

CREATE INDEX idx_email ON users(email)

Same query: 5 milliseconds.

1000x speedup. One line of SQL.

Composite Indexes

Sometimes you need multiple columns:

-- Slow
SELECT * FROM orders WHERE user_id = 123 AND status = 'pending'

-- Add composite index
CREATE INDEX idx_user_status ON orders(user_id, status)

-- Now fast

Column order matters. Put the most-filtered column first.

Monitoring

Check your slow queries:

-- Show slow queries (MySQL)
SELECT * FROM mysql.slow_log

-- Analyze a query (PostgreSQL)
EXPLAIN SELECT * FROM orders WHERE user_id = 123

If you see a full table scan (Seq Scan in PostgreSQL): add an index.

Gotchas

1. Index doesn't always help

-- Doesn't help:
SELECT * FROM users WHERE age > 18

-- Why? Age has millions of possible values.
-- Index is slower than scan for broad queries.

2. Indexes get fragmented
Over time, indexes become fragmented. Rebuild occasionally:

REINDEX TABLE users

3. Too many indexes slow down writes
Each write must update every index. More indexes = slower inserts/updates.

Checklist

Before deploying a query-heavy feature:

• [ ] Identify slow queries (EXPLAIN ANALYZE)
• [ ] Add indexes for WHERE/JOIN/ORDER BY columns
• [ ] Test query performance (should be < 100ms)
• [ ] Monitor index size (shouldn't be huge)
• [ ] Plan for index maintenance (rebuild periodically)

---

If your queries are slow, check for missing indexes before blaming the database.

90% of "slow database" problems are just missing indexes.

Add the right index. Problem solved.