-- Not indexing foreign key columns

SELECT * FROM customers c

JOIN orders o ON c.customer_id = o.customer_id;

-- No index on orders.customer_id

-- Index all join columns

CREATE INDEX idx_orders_customer ON orders(customer_id);

SELECT * FROM customers c

JOIN orders o ON c.customer_id = o.customer_id;

Index foreign key columns (orders.customer_id) and primary keys for optimal join performance.

Missing indexes on join columns cause full table scans

-- Using SELECT * with joins

SELECT * FROM customers c

JOIN orders o ON c.customer_id = o.customer_id

JOIN order_items oi ON o.order_id = oi.order_id;

-- Select only needed columns

SELECT c.customer_name, o.order_date, oi.quantity

FROM customers c

JOIN orders o ON c.customer_id = o.customer_id

JOIN order_items oi ON o.order_id = oi.order_id;

Select only needed columns to reduce data transfer and enable covering indexes.

SELECT * transfers unnecessary data and prevents covering index usage