Database Indexing Basics and Access Patterns

Index = extra data structure ordered by one or more keys so the engine can avoid scanning every row or document.

# Indexes trade storage and write cost for faster reads.

-- Better index candidate
WHERE email = ?

-- Often weaker candidate by itself
WHERE is_active = true

# Columns with many distinct values tend to benefit more.

More indexes => faster reads, slower writes, more storage, longer maintenance operations.

# Every extra index must be maintained during inserts, updates, and deletes.

INDEX (status, created_at, user_id)

Supports well:
WHERE status = ?
WHERE status = ? AND created_at >= ?

Usually not ideal alone for:
WHERE created_at >= ?

# Use column order that matches real filter patterns.

-- Common candidates
JOIN orders o ON o.user_id = u.id
ORDER BY created_at DESC

# Indexing is not just for filters.

1. Is the query slow enough to matter?
2. Is the pattern common in production?
3. Is the filter selective?
4. Can one composite index cover multiple common queries?
5. Does EXPLAIN show scans or expensive sorts?
6. What write and storage cost will this add?

# Use this checklist before creating a new index.

EXPLAIN SELECT * FROM orders WHERE status = 'paid' ORDER BY created_at DESC;

# Validate improvement instead of guessing.