-- Using functions on indexed columns prevents index usage

SELECT *

FROM orders o

JOIN price_history p ON o.product_id = p.product_id

AND YEAR(o.order_date) = YEAR(p.start_date);

-- Keep join conditions simple to allow index usage

SELECT *

FROM orders o

JOIN price_history p ON o.product_id = p.product_id

AND o.order_date >= p.start_date

AND o.order_date <= p.end_date;

YEAR() function prevents index usage on order_date. Use range conditions instead for better performance.

Functions on join columns prevent index usage

-- Too loose conditions create cartesian product

SELECT *

FROM customers c

JOIN promotions p ON c.total_spent > 0 -- Too broad condition

AND p.status = 'active';

-- Specific conditions prevent cartesian product

SELECT *

FROM customers c

JOIN promotions p ON (

(p.customer_type = 'vip' AND c.total_spent > p.min_spend_amount)

OR

(p.customer_type = 'new' AND c.registration_date >= p.target_date)

)

AND p.status = 'active';

Broad conditions like total_spent > 0 match almost all customers with all promotions, creating performance issues.

Loose join conditions create too many matches

-- Not indexing all columns in complex conditions

-- Missing indexes on start_date, end_date

SELECT *

FROM orders o

JOIN price_history p ON o.product_id = p.product_id

AND o.order_date BETWEEN p.start_date AND p.end_date;

-- Create indexes on all join condition columns

CREATE INDEX idx_price_history_product_dates

ON price_history(product_id, start_date, end_date);

CREATE INDEX idx_orders_product_date

ON orders(product_id, order_date);