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 namecustomers
. - Convert askterisk (*) to a list column names
customerName
,firstName
,lastName
,phone
, which corresponds tocustomerName
,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
.