MySQL executes joins between tables using a nested-loop algorithm or variations on it.
A simple nested-loop join (NLJ) algorithm reads rows from the first table in a loop one at a time, passing each row to a nested loop that processes the next table in the join. This process is repeated as many times as there remain tables to be joined.
Assume that a join between three tables
t1
, t2
, and
t3
is to be executed using the following
join types:
Press CTRL+C to copyTable Join Type t1 range t2 ref t3 ALL
If a simple NLJ algorithm is used, the join is processed like this:
Press CTRL+C to copyfor each row in t1 matching range { for each row in t2 matching reference key { for each row in t3 { if row satisfies join conditions, send to client } } }
Because the NLJ algorithm passes rows one at a time from outer loops to inner loops, it typically reads tables processed in the inner loops many times.