Menu
With the Temptable option MySQL will run the View Query and store the results in a temporary table. Your query will then be run against this table. This can be absolutely dire for performance if you don't have a where clause that significantly narrows down the result set in the View Query.
No, a view is simply a stored text query. You can apply WHERE and ORDER against it, the execution plan will be calculated with those clauses taken into consideration.
The optional ALGORITHM clause for CREATE VIEW or ALTER VIEW is a MySQL extension to standard SQL. It affects how MySQL processes the view. ALGORITHM takes three values: MERGE , TEMPTABLE , or UNDEFINED .
Because MySQL views look and function like regular tables, they are sometimes called virtual tables. Views offer a number of advantages. You can use views to hide table columns from users by granting them access to the view and not to the table itself. This helps enhance database security and integrity.
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?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With