Index Optimization for Joins

basic

Optimize slow customer-order join with proper indexing

-- Before: Slow query without indexes

SELECT c.customer_name, o.order_date, o.total_amount

FROM customers c

JOIN orders o ON c.customer_id = o.customer_id

WHERE o.order_date >= '2024-01-01';

-- EXPLAIN shows full table scans

-- Solution: Create proper indexes

CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

CREATE INDEX idx_customers_pk ON customers(customer_id);

-- After: Fast query with index usage

SELECT c.customer_name, o.order_date, o.total_amount

FROM customers c

JOIN orders o ON c.customer_id = o.customer_id

WHERE o.order_date >= '2024-01-01';

-- EXPLAIN shows index usage

Query performance improves from seconds to milliseconds

Composite index on (customer_id, order_date) supports both join and WHERE filter efficiently.

All

Covering Index Optimization

intermediate

Eliminate table lookups with covering indexes

-- Query needs customer_id, order_date, total_amount

SELECT o.customer_id, o.order_date, o.total_amount

FROM orders o

WHERE o.customer_id IN (1, 2, 3, 4, 5);

-- Create covering index with all needed columns

CREATE INDEX idx_orders_covering

ON orders(customer_id, order_date, total_amount);

-- Query now uses index-only scan (no table access)

EXPLAIN SELECT o.customer_id, o.order_date, o.total_amount

FROM orders o

WHERE o.customer_id IN (1, 2, 3, 4, 5);

Index-only scan eliminates table access, improving performance significantly

Covering index contains all columns needed by query, eliminating table lookups.

All