[mySQL5.5] 17장. Views

n  View Algorithm

 

①     UNDEFINED : default algorithm
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.

②     MERGE :
handled by merging corresponding parts of a view definition into the statement that refers to the view
If the MERGE algorithm cannot be used, a temporary table must be used instead

      MERGE 안되는 경우:

ü  Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)

ü  DISTINCT

ü  GROUP BY

ü  HAVING

ü  LIMIT

ü  UNION or UNION ALL

ü  Subquery in the select list

ü  Refers only to literal values (in this case, there is no underlying table)

 

③     TEMPTABLE :
the results from the view are retrieved into a temporary table, which then is used to execute the statement.
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.