MENU


Algorithms

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. MERGE is usually more efficient than TEMPTABLE. For example:

CREATE
ALGORIGHM=MERGE
VIEW vw(vc1,vc2) AS
SELECT c1,c2 FROM tbl WHERE c3>10;

If the query is:
SELECT c1,c2 FROM vw WHERE vc1<50;

The resulting statement to be executed is:
SELECT c1,c2 FROM tbl WHERE (c3>10) and (c1<50);


For TEMPTABLE, the results from the view are retrieved into a temporary table, which then is used to execute the statement. This allows locks to be released on underlying tables after the temporary table has been created and before it is used to finish processing the statement. The view cannot be updated.

For UNDEFINED(default), MySQL chooses which algorithm to use, preferring MERGE over TEMPTABLE.