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:
Table Join Type
t1 range
t2 ref
t3 ALL
If a simple NLJ algorithm is used, the join is processed like this:
for 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.