SLOW QUERY ANALYSIS

Original Query: 45.2s execution time

EXECUTION PLAN BREAKDOWN

Full table scan on orders (50M rows): 38.4s ← BOTTLENECK
Hash join on customers: 4.1s
Sort and filter: 2.7s

ROOT CAUSE

No index on orders(status, created_at)
Query filtering on status='active' AND created_at > date
Database must scan all 50M rows

OPTIMIZED QUERY

ADD INDEX:
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

Rewritten query uses index for both status and date filtering:

SELECT o.id, c.name, o.amount
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'active'
  AND o.created_at > NOW() - INTERVAL 30 days
ORDER BY o.created_at DESC;

RESULT: 1.3s execution time (34x improvement)

BREAKDOWN

Index seek: 0.8s
Join: 0.4s
Sort: 0.1s

PARTITIONING STRATEGY (If index not enough)

Partition orders table by month on created_at
Prunes 97% of partitions for 30-day queries
Combined with index: 200ms execution time

MONITOR

Add query to slow-query log alerts
Recheck index usage monthly