Optimizer Join Order Analysis

basic

Understand how optimizer chooses join order based on table sizes

-- Query with multiple tables of different sizes

EXPLAIN SELECT

c.customer_name,

o.order_date,

p.product_name

FROM customers c -- 10,000 rows

JOIN orders o ON c.customer_id = o.customer_id -- 100,000 rows

JOIN order_items oi ON o.order_id = oi.order_id -- 500,000 rows

JOIN products p ON oi.product_id = p.product_id; -- 1,000 rows

-- Optimizer typically chooses: products → customers → orders → order_items

-- (smallest to largest, considering selectivity)

EXPLAIN shows optimizer-chosen join order, typically starting with smallest/most selective table

Optimizer analyzes table statistics and chooses optimal join order. Usually processes smaller tables first.

All

Influencing Join Order with Subqueries

intermediate

Force specific join order when optimizer choice is suboptimal

-- Force processing of filtered orders first

SELECT

c.customer_name,

recent_orders.order_date,

recent_orders.total_amount

FROM (

SELECT customer_id, order_date, total_amount

FROM orders

WHERE order_date >= CURRENT_DATE - INTERVAL 30 DAY

AND total_amount > 1000

) recent_orders

JOIN customers c ON recent_orders.customer_id = c.customer_id

WHERE c.status = 'active';

-- Alternative: let optimizer decide

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 >= CURRENT_DATE - INTERVAL 30 DAY

AND o.total_amount > 1000

AND c.status = 'active';