MySQL TEMPTABLE vs MERGE – View Performance Comparison

In this tutorial, we will discuss the difference between the view processing algorithms merge and temptable. This is going to be a comparative guide on how these two view processing algorithms differ from each other by performance and use cases. So, let’s get started!

Also read: MySQL ALTER View

Introduction to the MERGE Algorithm

When using MERGE, the text of a statement that refers to a view and the view definition are combined such that matching parts of the statement are replaced by the view definition.

As was already noted, the statement that relates to the view is handled by merging appropriate sections of a view definition.

The MERGE algorithm’s operation is briefly demonstrated in the examples below. The examples presuppose the existence of a view v_merge with the following definition: (reference)

CREATE ALGORITHM = MERGE VIEW v_merge (vc1, vc2) AS
SELECT c1, c2 FROM t WHERE c3 > 100;Code language: SQL (Structured Query Language) (sql)

Now, if we execute the following statement,

SELECT * FROM v_merge;Code language: SQL (Structured Query Language) (sql)

This will be processed as-

• v_merge becomes t
• * becomes vc1, vc2, which corresponds to c1, c2
• The view WHERE clause is added

And the final statement becomes-

SELECT c1, c2 FROM t WHERE c3 > 100;Code language: SQL (Structured Query Language) (sql)

Introduction to the TEMPTABLE Algorithm

When using TEMPTABLE, the view’s results are loaded into a temporary table, which is then used to execute the statement.

One benefit of specifically specifying TEMPTABLE is that locks on underlying tables can be removed after the temporary table has been created but before it is used to complete the statement’s processing.

Using the temptable algorithm, lock release will take place quicker than the merge algorithm, so that other clients using the view will not be blocked.

MERGE vs TEMPTABLE Comparison

Generally speaking, the MERGE algorithm is recommended since it doesn’t delay the creation of temporary tables and lets your view use table indexes (as TEMPTABLE does).

In fact, this is what the MySQL Optimizer performs by default when a view’s algorithm is UNDEFINED (as it is by default), using MERGE if possible and TEMPABLE otherwise.

One thing to keep in mind is that if your view contains any of the following constructs, MySQL will not use the MERGE algorithm:

• 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
• References only to literal values (in this case, there is no underlying table)

The use of TEMPTABLE in this situation may result in performance problems for no apparent reason. In this situation, a stored procedure or subquery is preferable to a view.

Conclusion

In this article, we have talked about the difference between the merge algorithm and the temptable algorithm.

We have talked about its working as well as performance and compared which is more suitable and why. I hope you found this article helpful. If you do, don’t forget to share it with your friends!

References

MySQL official documentation on view algorithms.

Stackoverflow thread on merge vs temptable algorithm.