The Engineering Playbook: Indexing Strategies for MySQL & PostgreSQL

Understanding the Query Optimizer

When databases execute an SQL query, the query planner evaluates available access paths. Without appropriate indexing, the engine is forced to scan every page in the table—a catastrophic bottleneck once tables grow past millions of records.

Composite Index Left-to-Right Rule

Composite indexes on multiple columns (status, created_at, user_id) only accelerate queries that match the leading columns of the index:

• WHERE status = ? AND created_at >= ? → Full index hit
• WHERE created_at >= ? → Index skipped / Full scan

Analyzing Execution Plans with EXPLAIN ANALYZE

Always inspect the real execution cost before deploying migration changes. Pay close attention to Filter costs, temporary disk spills during sorting, and rows examined versus rows returned.