(Author: Pavithra Pandith, per request from SergeiP ) 

===========Mysql : 
tables t1 and t2 will be scanned only once (ST1). Hash join code
will partition the data and dump it into a temp file.



For tables : source hash-join-tables.sql
Query: explain analyze SELECT * FROM t1 LEFT JOIN t2 ON t1.i1=t2.i2;



t1-201 rows
t2 -50000 rows

t1 size = 81920
t2 size = 16269312

LEFT Hash join:
---------------
Right table is used to build the hash table in the join buffer i.e t2
Left table is used as the probe input i.e t1

case1)
------
Default join_buffer=262144 i.e 256MB (Hash join)
Time:0.09sec
Created_tmp_files =256
Handler_read_rnd_next= 50203

case2)
-------
join_buffer_size=1024 (Hash join)
Time: 0.12 sec
Created_tmp_files =906
Handler_read_rnd_next= 50203

case3)
------
join_buffer= Half of t2 =8134656
32 tmp files created   (8134656/262144 ~= 32)
Handler_read_rnd_next= 50203



case4)
-------
join_buffer_size=16269312  (Whole t2 can fit in memory)
Time: 0.06sec
No tmp files created
Handler_read_rnd_next= 50203





Observations:
-------------
case1) and case2) Hash table can't fit in memory i.e Table t2 size >join_buffer_size
Tables are scanned only once
i.e Handler_read_rnd_next= 50203 ( t1 + t2 rows =50201)
Tmp files created.

case4) Whole t2 can fit in memory ,hence no tmp files created ,tables scanned only once