View Processing Algorithms 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: 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: Example 1: Suppose that we issue this statement: MySQL handles the statement as follows: The resulting statement to be executed becomes: Example 2: Suppose that we issue this statement: 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: Effectively, the statement to be executed has a  WHERE  clause of this form: 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. 4746 •The view  WHERE  clause is added • *  becomes  vc1, vc2 , which corresponds to  c1, c2 • v_merge  becomes  t •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 . CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS SELECT c1, c2 FROM t WHERE c3 > 100; SELECT * FROM v_merge; SELECT c1, c2 FROM t WHERE c3 > 100; SELECT * FROM v_merge WHERE vc1 < 100; SELECT c1, c2 FROM t WHERE (c3 > 100) AND (c1 < 100); WHERE (select WHERE) AND (view WHERE)