Advanced MySQL Features - High Performance MySQL - page 278

the view. This is not a complete list, and it might change in the future. If you want to
know whether a view will use MERGE or TEMPTABLE , you can EXPLAIN a trivial SELECT query
against the view:
mysql> EXPLAIN SELECT * FROM <view_name> ;
+----+-------------+
| id | select_type |
+----+-------------+
| 1 | PRIMARY |
| 2 | DERIVED |
+----+-------------+
The presence of a SELECT type of DERIVED select type indicates that the view will use the
TEMPTABLE algorithm. Beware, though: if the underlying derived table is expensive to
produce, EXPLAIN can be quite costly and slow to execute in MySQL 5.5 and older
versions, because it will actually execute and materialize the derived table.
The algorithm is a property of the view and is not influenced by the type of query that
is executed against the view. For example, suppose you create a trivial view and ex-
plicitly specify the TEMPTABLE algorithm:
CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM sakila.actor;
The SQL inside the view doesn't inherently require a temporary table, but the view will
always use one, no matter what type of query you execute against it.
Updatable Views
An updatable view lets you update the underlying base tables via the view. As long as
specific conditions hold, you can UPDATE , DELETE , and even INSERT into a view as you
would with a normal table. For example, the following is a valid operation:
mysql> UPDATE Oceania SET Population = Population * 1.1 WHERE Name = 'Australia';
A view is not updatable if it contains GROUP BY , UNION , an aggregate function, or any of
a few other exceptions. A query that changes data might contain a join, but the columns
to be changed must all be in a single table. Any view that uses the TEMPTABLE algorithm
is not updatable.
The CHECK OPTION clause, which we included when we created the view in the previous
section, ensures that any rows changed through the view continue to match the view's
WHERE clause after the change. So, we can't change the Continent column, nor can we
insert a row that has a different Continent . Either would cause the server to report an
error:
mysql> UPDATE Oceania SET Continent = 'Atlantis';
ERROR 1369 (HY000): CHECK OPTION failed 'world.Oceania'
Some database products allow INSTEAD OF triggers on views so you can define exactly
what happens when a statement tries to modify a view's data, but MySQL does not
support triggers on views.