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, 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.
TEMPTABLE, the results from the view are retrieved into a temporary table, which then is used to execute the statement.
UNDEFINED, MySQL chooses which algorithm to use. It prefers
TEMPTABLEif possible, because
MERGEis usually more efficient and because a view cannot be updatable if a temporary table is used.
ALGORITHMclause is present, the default algorithm is determined by the value of the
derived_mergeflag of the
optimizer_switchsystem variable. For additional discussion, see Section 22.214.171.124, “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
ALGORITHMclause is present in the
CREATE VIEWstatement has an explicit
ALGORITHM = UNDEFINEDclause.
ALGORITHM = MERGEis 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
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
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:
vc1, vc2, which corresponds to
WHEREclause 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
vc1 < 100 becomes
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
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)
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
LIMIT in the subquery.
For details, see Section 126.96.36.199, “Optimizing Derived Tables, View References, and Common Table Expressions
with Merging or Materialization”.