I have a view which queries from 2 tables that don't change often (they are updated once or twice a day) and have a maximum of 2000 and 1000 rows).
Which algorithm should perform better, MERGE or TEMPTABLE?
Wondering, will MySQL cache the query result, making TEMPTABLE the best choice in my case?
Reading https://dev.mysql.com/doc/refman/5.7/en/view-algorithms.html I understood that basically, the MERGE algorithm will inject the view code in the query that is calling it, then run. The TEMPTABLE algorithm will make the view run first, store its result into a temporary table then used. But no mention to cache.
I know I have the option to implement Materialized Views myself (http://www.fromdual.com/mysql-materialized-views). Can MySQL automatically cache the TEMPTABLE result and use it instead?
Generally speaking the MERGE algorithm is preferred as it allows your view to utilize table indexes, and doesn't introduce a delay in creating temporary tables (as TEMPTABLE does).
In fact this is what the MySQL Optimizer does by default - when a view's algorithm UNDEFINED (as it is by default) MySQL will use MERGE if it can, otherwise it'll use TEMPTABLE.
One thing to note (which has caused me a lot of pain) is that MySQL will not use the MERGE algorithm if your view contains any of the following constructs:
Constructs that prevent merging are the same for derived tables and view references:
Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)
DISTINCT
GROUP BY
HAVING
LIMIT
UNION or UNION ALL
Subqueries in the select list
Assignments to user variables
Refererences only to literal values (in this case, there is no underlying table)
In this case, TEMPTABLE will be used, which can cause performance issues without any clear reason why. In this case it's best to use a stored procedure, or subquery instead of a view
Thank's MySQL ðŸ˜