MySQL View Processing Algorithms

In this tutorial, we will learn about the view processing algorithm present in MySQL which is used to change the way to process the views. It is super easy to understand as well as implement. So, let’s get started!

Also read: Create Views in MySQL – A Complete Guide

Introduction to View Processing Algorithms

There are three view processing algorithms present in MySQL which you can use to modify the default behaviour of processing views. You can set the algorithm using the ALGORITHM clause for the CREATE VIEW or ALTER VIEW statements.

The ALGORITHM clause can have one of the three values of the following: MERGE, TEMPTABLE, UNDEFINED.

We will see the syntax of specifying the algorithm and then we will see the information of each algorithm one by one.

Syntax of ALGORITHM Clause

To specify the algorithm while creating a view, the following syntax is used-

CREATE [OR REPLACE][ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}] VIEW 
view_name[(column_list)]
AS  select-statement;Code language: SQL (Structured Query Language) (sql)

On the other hand, to specify the algorithm while updating a view, the following syntax is used-

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement;
Code language: SQL (Structured Query Language) (sql)

MERGE Algorithm

The following stages are carried out by MySQL when processing a query from a MERGE view:

First, combine the input query with the view definition’s SELECT statement to create a single query.
After that, run the merged query to get the result set.

Keep in mind that view resolution refers to the combining of the input query and the SELECT statement from the view definition into a single query.

For example,

take an example of the following table.

Let’s create a view having the MERGE algorithm.

CREATE ALGORITHM=MERGE VIEW empsData
AS SELECT * FROM emps;Code language: SQL (Structured Query Language) (sql)

Now, let’s run the following query to find the names of employees having the letter ‘a’ in their name.

SELECT * FROM empsData
WHERE name LIKE '%a%';Code language: SQL (Structured Query Language) (sql)

Here, MySQL will perform the following steps to find the records-

• First, the view name will be converted into the table name.
• Second, the * will find all column names.
• Finally, the WHERE clause will be applied.

TEMPTABLE Algorithm

The following actions are carried out by MySQL when you execute a query to a TEMPABLE view:

First, the result retrieved from the select statement in the view is stored in the temporary table. Later, the input query is executed on that temporary table.

Here, MySQL will first retrieve the data and copy the data from the base table to the temporary table, which makes it time expensive. That’s why it is less efficient than the merge algorithm.

Note that, the temptable views are not updatable.

UNDEFINED Algorithm

When you create a view without providing the ALGORITHM clause or you specifically indicate ALGORITHM=UNDEFINED, the UNDEFINED algorithm is used by default.

Furthermore, MySQL sets the algorithm to UNDEFINED and issues a warning when you create a view with ALGORITHM = MERGE and MySQL can only execute the view using a temporary table.

MySQL has the option of choosing MERGE or TEMPTABLE from the UNDEFINED. And because MERGE frequently performs better than TEMPTABLE, MySQL favours it whenever feasible.

Conclusion

In this tutorial, we have learned about view processing algorithms and how to change the algorithms while creating as well as modifying the views. As I said, it is very easy to understand and implement the view processing algorithms without any trouble. If you think this tutorial helped you learn something new, don’t forget to share it with your friends.

References

MySQL official documentation on view algorithms.