Optimizing SELECT Statements IF P(t1,NULL,NULL) { t:=t1||NULL||NULL; OUTPUT t; } } } FOR each row t1 in T1 { BOOL f1:=FALSE; FOR each row t3 in T3 such that P2(t1,t3) { FOR each row t2 in T2 such that P1(t1,t2) { IF P(t1,t2,t3) { t:=t1||t2||t3; OUTPUT t; } f1:=TRUE } } IF (!f1) { IF P(t1,NULL,NULL) { t:=t1||NULL||NULL; OUTPUT t; } } } P(T1,T2,T2) = C1(T1) AND C2(T2) AND C3(T3). FOR each row t1 in T1 such that C1(t1) { FOR each row t2 in T2 such that P1(t1,t2) AND C2(t2) { FOR each row t3 in T3 such that P2(t2,t3) AND C3(t3) { IF P(t1,t2,t3) { t:=t1||t2||t3; OUTPUT t; } } } } P(T1,T2,T3)=C1(T1) AND C(T2) AND C3(T3) FOR each row t1 in T1 such that C1(t1) { BOOL f1:=FALSE; FOR each row t2 in T2 1666 For that example, the nested-loop algorithm using guarded pushed-down conditions looks like this: Recall this example with outer joins: For a query with outer joins, the  WHERE  condition is to be checked only after it has been found that the current row from the outer table has a match in the inner tables. Thus, the optimization of pushing conditions out of the inner nested loops cannot be applied directly to queries with outer joins. Here we must introduce conditional pushed-down predicates guarded by the flags that are turned on when a match has been encountered. You see that each of the conjuncts  C1(T1) ,  C2(T2) ,  C3(T3)  are pushed out of the most inner loop to the most outer loop where it can be evaluated. If  C1(T1)  is a very restrictive condition, this condition pushdown may greatly reduce the number of rows from table  T1  passed to the inner loops. As a result, the execution time for the query may improve immensely. In this case, MySQL actually uses the following nested-loop algorithm for the execution of the query with inner joins: When discussing the nested-loop algorithm for inner joins, we omitted some details whose impact on the performance of query execution may be huge. We did not mention so-called “pushed-down”  conditions. Suppose that our  WHERE  condition  P(T1,T2,T3)  can be represented by a conjunctive formula: The other nesting evaluates  T3 , then  T2 :