Rechercher dans le manuel MySQL
24.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 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 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:
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:
Example 1: Suppose that we issue this statement:
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:
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. Examples are SELECT
DISTINCT
or LIMIT
in the subquery.
For details, see Section 8.2.2.4, “Optimizing Derived Tables, View References, and Common Table Expressions
with Merging or Materialization”.
Document created the 26/06/2006, last modified the 26/10/2018
Source of the printed document:https://www.gaudry.be/en/mysql-rf-view-algorithms.html
The infobrol is a personal site whose content is my sole responsibility. The text is available under CreativeCommons license (BY-NC-SA). More info on the terms of use and the author.
References
These references and links indicate documents consulted during the writing of this page, or which may provide additional information, but the authors of these sources can not be held responsible for the content of this page.
The author This site is solely responsible for the way in which the various concepts, and the freedoms that are taken with the reference works, are presented here. Remember that you must cross multiple source information to reduce the risk of errors.