MySQL View Processing Algorithms

Created with Sketch.

MySQL View Processing Algorithms

Summary: in this tutorial, you will learn about MySQL view processing algorithms including MERGE, TEMPTABLE, and UNDEFINED.

The CREATE VIEW and ALTER VIEW statements have an optional clause: ALGORITHM. The algorithm determines how MySQL process a view and can take one of three values MERGE, TEMPTABLE, and UNDEFINE.

Here is the CREATE VIEW statement with the ALGORITHM clause:

CREATE [OR REPLACE][ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}] VIEW
view_name[(column_list)]
AS
select-statement;

Code language: SQL (Structured Query Language) (sql)

And this is the ALTER VIEW statement with the ALGORITHM clause:

CREATE [ALGORITHM = {MERGE | TEMPTABLE | UNDEFINED}] VIEW
view_name[(column_list)]
AS
select-statement;

Code language: SQL (Structured Query Language) (sql)

MERGE

When you query from a MERGE view, MySQL processes the following steps:

  • First, merge the input query with the SELECT statement in the view definition into a single query.
  • Then, execute the combined query to return the result set.

Note that the combination of input query and the SELECT statement of the view definition into a single query is referred to as view resolution.

See the following customers from the sample database:

 

The following statement creates a view based on the customers table with the name contactPersons with the MERGE algorithm:

CREATE ALGORITHM=MERGE VIEW contactPersons(
customerName,
firstName,
lastName,
phone
) AS
SELECT
customerName,
contactFirstName,
contactLastName,
phone
FROM customers;

Code language: SQL (Structured Query Language) (sql)

Suppose that you issue the following statement:

SELECT * FROM contactPersons
WHERE customerName LIKE '%Co%';

Code language: SQL (Structured Query Language) (sql)

MySQL performs these steps:

  • Convert view name contactPersons to table name customers.
  • Convert askterisk (*)  to a list column names customerName, firstName, lastName, phone, which corresponds to customerName, contactFirstName, contactLastName, phone.
  • Add the WHERE clause.

The resulting statement is:

SELECT
customerName,
contactFirstName,
contactLastName,
phone
FROM
customers
WHERE
customerName LIKE '%Co%';

Code language: SQL (Structured Query Language) (sql)

TEMPTABLE

When you issue a query to a TEMPTABLE view, MySQL performs these steps:

  • First, create a temporary table to store the result of the SELECT in the view definition.
  • Then, execute the input query against the temporary table.

Because MySQL has to create the temporary table to store the result set and moves the data from the base tables to the temporary table, the algorithm TEMPTABLE  is less efficient than the MERGE algorithm.

Note that TEMPTABLE views cannot be updatable.

UNDEFINED

The UNDEFINED is the default algorithm when you create a view without specifying the ALGORITHM clause or you explicitly specify ALGORITHM=UNDEFINED.

In addition, when you create a view with ALGORITHM = MERGE and MySQL can only process the view with a temporary table, MySQL automatically sets the algorithm to UNDEFINED and generates a warning.

TheUNDEFINED allows MySQL to choose either MERGE or TEMPTABLE. And MySQL prefers MERGE  over TEMPTABLE  if possible because MERGE is often more efficient than TEMPTABLE.

In this tutorial, you have learned about the MySQL view processing algorithms including MERGE, TEMPTABLE, and UNDEFINED.

Leave a Reply

Your email address will not be published. Required fields are marked *