Query rewriting transforms a slow query into a faster equivalent query that returns the same results. The key insight: SQL is declarative (you say WHAT you want, not HOW to get it), so multiple queries can produce identical results with vastly different performance.

Think of it like giving directions. Bad: "Go through every street in the city until you find the library." Good: "Take Main Street, turn left on Oak, library is on the right." Same destination, completely different approach.

**Why Query Rewriting Works:**

1. **Enables Index Usage**

- OR conditions often prevent index usage

- UNION ALL allows each branch to use different indexes

- Result: Full scan → Index scan

2. **Eliminates Nested Loops**

- Subqueries execute once per outer row (nested loop)

- JOINs execute once total (hash/merge join)

- Result: O(n*m) → O(n+m)

3. **Avoids NULL Pitfalls**

- NOT IN fails with NULLs (returns no rows)

- LEFT JOIN + IS NULL handles NULLs correctly

- Result: Wrong results → Correct results + faster

**Common Rewriting Patterns:**

**Pattern 1: OR to UNION ALL**

Problem: OR conditions prevent index usage

Slow (can't use indexes efficiently):

SELECT * FROM orders

WHERE status = 'pending' OR priority = 'urgent';

-- Can't use index on status AND index on priority

-- Must scan entire table

-- Time: 15 seconds on 10M rows

Fast (each branch uses its index):

SELECT * FROM orders WHERE status = 'pending'

UNION ALL

SELECT * FROM orders WHERE priority = 'urgent' AND status != 'pending';

-- First query uses index on status

-- Second query uses index on priority

-- Time: 0.5 seconds (30x faster)

When to use:

- OR conditions on different columns

- Each column has an index

- Conditions are selective (filter out most rows)

**Pattern 2: Subquery to JOIN**

Problem: Correlated subqueries execute once per row

Slow (nested loop execution):

SELECT c.customer_name,

(SELECT COUNT(*) FROM orders WHERE customer_id = c.id) as order_count