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 prefersMERGE
overTEMPTABLE
if possible, becauseMERGE
is usually more efficient and because a view cannot be updatable if a temporary table is used.If no
ALGORITHM
clause is present, the default algorithm is determined by the value of thederived_merge
flag of theoptimizer_switch
system variable. For additional discussion, see Section 10.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 theCREATE VIEW
statement.The
CREATE VIEW
statement has an explicitALGORITHM = 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 toUNDEFINED
.
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
becomest
*
becomesvc1, vc2
, which corresponds toc1, 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. Examples are SELECT
DISTINCT
or LIMIT
in the subquery.
For details, see Section 10.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions
with Merging or Materialization”.