E-commerce Order Query Optimization

intermediate

Optimize a slow customer order history query that takes 45 seconds on a table with 10M orders

-- BEFORE: Unoptimized query (45 seconds)

SELECT *

FROM orders o

JOIN customers c ON o.customer_id = c.customer_id

JOIN order_items oi ON o.order_id = oi.order_id

JOIN products p ON oi.product_id = p.product_id

WHERE YEAR(o.order_date) = 2024

AND c.country = 'USA'

ORDER BY o.order_date DESC;

-- STEP 1: Analyze execution plan

EXPLAIN ANALYZE

SELECT *

FROM orders o

JOIN customers c ON o.customer_id = c.customer_id

JOIN order_items oi ON o.order_id = oi.order_id

JOIN products p ON oi.product_id = p.product_id

WHERE YEAR(o.order_date) = 2024

AND c.country = 'USA'

ORDER BY o.order_date DESC;

-- Execution Plan shows:

-- Seq Scan on orders (cost=0..250000 rows=10000000)

-- Seq Scan on customers (cost=0..50000 rows=2000000)

-- Problem: Full table scans, function on indexed column

-- STEP 2: Create strategic indexes

CREATE INDEX idx_orders_date ON orders(order_date);

CREATE INDEX idx_orders_customer ON orders(customer_id, order_date);

CREATE INDEX idx_customers_country ON customers(country, customer_id);

CREATE INDEX idx_order_items_order ON order_items(order_id);

CREATE INDEX idx_order_items_product ON order_items(product_id);

-- STEP 3: Optimize query structure

-- AFTER: Optimized query (0.8 seconds)

SELECT

o.order_id,

o.order_date,

o.total_amount,

c.customer_name,

c.email,