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
. The default algorithm is
UNDEFINED
if no ALGORITHM
clause is present.
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.
A reason to choose 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. MERGE
cannot be used if the view contains any of the following
constructs:
User Comments
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).
Add your own comment.