Section 8.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions with Merging or Materialization” . 

A reason to specify TEMPTABLE  explicitly is that locks can be released on underlying tables after the temporary table has been created and before it is used to finish processing the statement. This might result in quicker lock release than the MERGE  algorithm so that other clients that use the view are not blocked as long. 

A view algorithm can be UNDEFINED  for three reasons: 

•No ALGORITHM  clause is present in the CREATE VIEW  statement. 

•The CREATE VIEW  statement has an explicit ALGORITHM = UNDEFINED  clause. 

• ALGORITHM = MERGE  is specified for a view that can be processed only with a temporary table. In this case, MySQL generates a warning and sets the algorithm to UNDEFINED . 

As mentioned earlier, MERGE  is handled by merging corresponding parts of a view definition into the statement that refers to the view. The following examples briefly illustrate how the MERGE  algorithm works. The examples assume that there is a view v_merge  that has this definition: 

CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS SELECT c1, c2 FROM t WHERE c3 > 100; 

Example 1: Suppose that we issue this statement: 

SELECT * FROM v_merge; 

MySQL handles the statement as follows: 

• v_merge  becomes t 

• *  becomes vc1, vc2 , which corresponds to c1, c2 

•The view WHERE  clause is added 

The resulting statement to be executed becomes: 

SELECT c1, c2 FROM t WHERE c3 > 100; 

Example 2: Suppose that we issue this statement: 

SELECT * FROM v_merge WHERE vc1 < 100; 

This statement is handled similarly to the previous one, except that vc1 < 100  becomes c1 < 100  and the view WHERE  clause is added to the statement WHERE  clause using an AND  connective (and parentheses are added to make sure the parts of the clause are executed with correct precedence). The resulting statement to be executed becomes: 

SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100); 

Effectively, the statement to be executed has a WHERE  clause of this form: 

WHERE (select WHERE) AND (view WHERE) 

If the MERGE  algorithm cannot be used, a temporary table must be used instead. Constructs that prevent merging are the same as those that prevent merging in derived tables and common table expressions. 

