Project: Optimizing a 1,000,000 Row Query

Congratulations! You've completed **Phase 7**. You are now a "Performance Engineer."

Today, we take a broken, slow query and fix it.

The "Before" Query (10 Seconds)

SELECT * FROM orders

JOIN users ON orders.user_id = users.id

WHERE users.country = 'USA'

AND orders.status = 'Pending';

The Optimization Plan

1. **Remove `SELECT *`**: We only need `order_id` and `user_email`.

2. **Double-check Joins**: Ensure `orders.user_id` has an index.

3. **Partial Index**: Create an index on `status` for only 'Pending' orders.

4. **Composite Index**: Create an index on `users` for `country`.

The "After" Result (0.1 Seconds)

-- Updated Query

SELECT o.id, u.email

FROM orders o

JOIN users u ON o.user_id = u.id

WHERE u.country = 'USA'

AND o.status = 'Pending';

-- Supporting Indexes

CREATE INDEX idx_pending_orders ON orders (user_id) WHERE status = 'Pending';

CREATE INDEX idx_users_country ON users (country);

Why it's 100x faster

We reduced the data we're pulling (`SELECT *`), ensured the Join was using an index, and used a Partial Index to ignore the millions of non-pending orders.

**Next Phase (Days 96–100): Career & Reality**. We're wrapping up the series by talking about the "Soft Skills" of SQL—getting a job, passing interviews, and surviving a real-life data crisis.

See you in the final stretch!