About VIEW in database ... What is the difference between the three algorithms when creating a VIEW with [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
? What does each one do?
About VIEW in database ... What is the difference between the three algorithms when creating a VIEW with [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
? What does each one do?
For UNDEFINED, MySQL chooses which algorithm to use.
He prefers MERGE over TempTable if possible, because MERGE is generally more efficient and because VIEW can not be updatable if a temporary table is used.
The reason for choosing TempTable is explicitly that locks can be flushed into underlying tables after the temporary table was created and before it is used to finish processing the statement.
This can result in faster lock release than the merge algorithm so that other clients that use the viewpoint are not blocked for so long.