|MySQL has two algorithms for processing a View--|
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
Last updated 16 Aug 2019