F.4. Restrictions on Views

View processing is not optimized:

It is not possible to create an index on a view.

Indexes can be used for views processed using the merge
algorithm. However, a view that is processed with the
temptable algorithm is unable to take advantage of indexes on
its underlying tables (although indexes can be used during
generation of the temporary tables).

Subqueries cannot be used in the FROM clause of
a view. This limitation will be lifted in the future.

If the view is evaluated using a temporary table, you
can select from the table in the view
subquery and still modify that table in the outer query. In this
case the view will be stored in a temporary table and thus you are
not really selecting from the table in a subquery and modifying it
“at the same time.” (This is another reason you might
wish to force MySQL to use the temptable algorithm by specifying
ALGORITHM = TEMPTABLE in the view definition.)

You can use DROP TABLE or ALTER
TABLE to drop or alter a table that is used in a view
definition (which invalidates the view) and no warning results
from the drop or alter operation. An error occurs later when the
view is used.

A view definition is “frozen” by certain statements:

If a statement prepared by PREPARE refers
to a view, the view contents seen each time the statement is
executed later will be the contents of the view at the time it
was prepared. This is true even if the view definition is
changed after the statement is prepared and before it is
executed. Example:

If a statement in a stored routine refers to a view, the view
contents seen by the statement are its contents the first time
that statement is executed. For example, this means that if
the statement is executed in a loop, further iterations of the
statement see the same view contents, even if the view
definition is changed later in the loop. Example:

When the procedure p() is called, the
SELECT returns 1 each time through the
loop, even though the view definition is changed within the
loop.

With regard to view updatability, the overall goal for views is
that if any view is theoretically updatable, it should be
updatable in practice. This includes views that have
UNION in their definition. Currently, not all
views that are theoretically updatable can be updated. The initial
view implementation was deliberately written this way to get
usable, updatable views into MySQL as quickly as possible. Many
theoretically updatable views can be updated now, but limitations
still exist:

Updatable views with subqueries anywhere other than in the
WHERE clause. Some views that have
subqueries in the SELECT list may be
updatable.

You cannot use UPDATE to update more than
one underlying table of a view that is defined as a join.

You cannot use DELETE to update a view that
is defined as a join.

There exists a shortcoming with the current implementation of
views. If a user is granted the basic privileges necessary to
create a view (the CREATE VIEW and
SELECT privileges), that user will be unable to
call SHOW CREATE VIEW on that object unless the
user is also granted the SHOW VIEW privilege.

That shortcoming can lead to problems backing up a database with
mysqldump, which may fail due to insufficient
privileges. This problem is described in Bug#22062.

The workaround to the problem is for the administrator to manually
grant the SHOW VIEW privilege to users who are
granted CREATE VIEW, since MySQL doesn't grant
it implicitly when views are created.