MySQL has two algorithms for processing a View--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 |
 |