／var／logmarcus chiu

Table Join Order Matters For Performance

Created on · Last Modified on

Bad Join Order

SELECT
  o.order_id,
  s.country_of_manufacture
FROM
  orders o						            -- 73595 rows
  INNER JOIN order_lines l			        -- 231412 rows
    ON o.order_id = l.order_id			    -- 231412 rows after join
  INNER JOIN stock_items s		            -- 227 rows
    ON l.stock_item_id = s.stock_item_id	-- 1036 rows after join 
	AND s.country_of_manufacture = 'USA'	-- 8 rows for USA

we want to join both stock_items and order_lines first, then orders table

Query Optimizer Automatically Does This For Us

  • table a has 4 rows
  • table b has 6 rows
  • table c has 0 rows
SELECT * FROM a INNER JOIN b ON a.id = b.a_id INNER JOIN c ON b.a_id = c.test;
SELECT * FROM a INNER JOIN c ON a.id = c.test INNER JOIN b ON b.a_id = c.test;
SELECT * FROM c INNER JOIN a ON a.id = c.test INNER JOIN b ON b.a_id = c.test;

all 3 SELECT statements results in the SAME execution plan

the Query Optimizer uses table statistics to quickly determine to best join order

Enforcing Join Order Despite Query Optimizer

there are tricks to enforce join order