-- BEFORE: Slow Query with Full Table Scan
SELECT *  -- Bad: Selecting all columns
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE YEAR(o.order_date) = 2024
AND c.country = 'USA'
ORDER BY o.total_amount DESC;

-- AFTER: Optimized Query
CREATE INDEX idx_orders_date_amount ON orders(order_date, total_amount);
CREATE INDEX idx_customers_country ON customers(country);

EXPLAIN ANALYZE  -- Use to analyze query plan
SELECT o.id, o.order_date, o.total_amount, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.order_date >= '2024-01-01'
AND o.order_date < '2025-01-01'
AND c.country = 'USA'
ORDER BY o.total_amount DESC
LIMIT 100;  -- Pagination for large datasets