14

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?

Paulo Amaral
  • 747
  • 1
  • 5
  • 24

2 Answers2

14

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

Community
  • 1
  • 1
5

Which algorithm? It depends on the particular query and schema. Usually the Optimizer picks the better approach, and you should not specify.

But... Sometimes the Optimizer picks really bad approach. At that point, the only real solution is not to use Views. That is, some Views cannot be optimized as well as the equivalent SELECT.

If you want to discuss a particular case, please provide the SHOW CREATE VIEW and SHOW CREATE TABLEs, plus a SELECT calling the view. And construct the equivalent SELECT. Also include EXPLAIN for both SELECTs.

Rick James
  • 135,179
  • 13
  • 127
  • 222