Indexes speed up reads but slow down writes. Here's when and how to use them effectively.

When to Create Indexes

• Primary keys: Automatically indexed
• Foreign keys: Almost always index these
• WHERE clause columns: Frequently filtered columns
• JOIN conditions: Both sides of the join
• ORDER BY columns: When sorting is slow

Composite Indexes (Multi-Column)

Use when queries filter on multiple columns together:

-- Query: WHERE user_id = 123 AND created_at > '2025-01-01'
CREATE INDEX idx_user_created ON orders(user_id, created_at);

-- Order matters! Most selective column first
-- user_id (high selectivity) before created_at (low selectivity)

The Left-Prefix Rule

Index on (user_id, created_at) can be used for:

• ✅ WHERE user_id = 123
• ✅ WHERE user_id = 123 AND created_at > '2025-01-01'
• ❌ WHERE created_at > '2025-01-01' (can't use the index)

When NOT to Index

• Small tables (< 1000 rows) — table scans are faster
• Low-selectivity columns (gender, boolean) — unless part of composite index
• Frequently updated columns — indexes slow down INSERTs/UPDATEs
• Tables with heavy write operations — each index adds write overhead

Quick Check: Is My Index Being Used?

EXPLAIN SELECT * FROM orders
WHERE user_id = 123 AND created_at > '2025-01-01';
-- Look for "index scan" not "sequential scan"