MERGE and TEMPTABLE. They each do what they say: MERGE merges the SQL of the View and of its underlying query; TEMPTABLE saves results of Views referenced in the query to temporary tables, then executes that.
If you don't specify ALGORITHM, MySQL will try to use MERGE. It decides it cannot if the query aggregates; or uses DISTINCT, GROUP BY, HAVING, LIMIT, UNION or user variables; or if a SELECT expression is a subquery; or has no table reference. If you specify MERGE and MySQL thinks it cannot use it, it will respond with a warning to that effect.
Some queries can be rewritten such that MySQL can use MERGE in Views that reference them. The most obvious case would be moving a subquery from the SELECT list to the FROM clause.
Last updated 16 Aug 2019
