20.5.2 View Processing Algorithms

The optional ALGORITHM clause for
CREATE VIEW or
ALTER VIEW is a MySQL extension to
standard SQL. It affects how MySQL processes the view.
ALGORITHM takes three values:
MERGE, TEMPTABLE, or
UNDEFINED.

For MERGE, the text of a statement that
refers to the view and the view definition are merged such
that parts of the view definition replace corresponding parts
of the statement.

For TEMPTABLE, the results from the view
are retrieved into a temporary table, which then is used to
execute the statement.

For UNDEFINED, MySQL chooses which
algorithm to use. It prefers MERGE over
TEMPTABLE if possible, because
MERGE is usually more efficient and because
a view cannot be updatable if a temporary table is used.

If no ALGORITHM clause is present,
UNDEFINED is the default algorithm.

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.

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:

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. MERGE
cannot be used if the view contains any of the following
constructs:

The limitations listed above on creating a view with ALGORITHM=MERGE apply only to the "top level" of the view being created, i.e. they do not apply to subqueries.

In other words, you can create a view (let's call it "parent") with ALGORITHM=MERGE that uses, in a subquery: a) MAX,GROUP BY, etc. b) a view (let's call it "child") defined with ALGORITHM=TEMPTABLE.

Queries or subsequent views using the "parent" view will be able to use any indices available from underlying tables accessed by the "parent" view, though not indices from tables accessed through the "child" view (since it's TEMPTABLE).