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
There is a general principle that you cannot modify a table and select from the same table in a subquery. See Chapter 4, Restrictions on Subqueries.
The same principle also applies if you select from a view that selects from the table, if the view selects from the table in a subquery and the view is evaluated using the merge algorithm. Example:
CREATE VIEW v1 AS SELECT * FROM t2 WHERE EXISTS (SELECT 1 FROM t1 WHERE t1.a = t2.a); UPDATE t1, v2 SET t1.a = 1 WHERE t1.b = v2.b;
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. No warning results from
even though this invalidates the view. Instead, an error occurs
later, when the view is used.
TABLE can be used to check for views that have been
A view definition is “frozen” by certain statements:
If a statement prepared by
PREPARErefers to a view, the view definition seen each time the statement is executed later will be the definition 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:
CREATE VIEW v AS SELECT RAND(); PREPARE s FROM 'SELECT * FROM v'; ALTER VIEW v AS SELECT NOW(); EXECUTE s;
The result returned by the
EXECUTEstatement is a random number, not the current date and time.
If a statement in a stored routine refers to a view, the view definition seen by the statement are its definition 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 definition, even if the definition is changed later in the loop. Example:
CREATE VIEW v AS SELECT 1; delimiter // CREATE PROCEDURE p () BEGIN DECLARE i INT DEFAULT 0; WHILE i < 5 DO SELECT * FROM v; SET i = i + 1; ALTER VIEW v AS SELECT 2; END WHILE; END; // delimiter ; CALL p();
When the procedure
p()is called, the
SELECTreturns 1 each time through the loop, even though the view definition is changed within the loop.
As of MySQL 5.1.21,
ALTER VIEWis prohibited within stored routines, so this restriction does not apply.
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. 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
Updatable views with subqueries anywhere other than in the
WHEREclause. Some views that have subqueries in the
SELECTlist may be updatable.
You cannot use
UPDATEto update more than one underlying table of a view that is defined as a join.
You cannot use
DELETEto 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.
Views do not have indexes, so index hints do not apply. Use of index hints when selecting from a view is not permitted.
SHOW CREATE VIEW displays view
definitions using an
clause for each
column. If a column is created from an expression, the default
alias is the expression text, which can be quite long. As of MySQL
5.1.23, aliases for column names in
VIEW statements are checked against the maximum column
length of 64 characters (not the maximum alias length of 256
characters). As a result, views created from the output of
SHOW CREATE VIEW fail if any column
alias exceeds 64 characters. This can cause problems in the
following circumstances for views with too-long aliases:
View definitions fail to replicate to newer slaves that enforce the column-length restriction.
Dump files created with mysqldump cannot be loaded into servers that enforce the column-length restriction.
A workaround for either problem is to modify each problematic view
definition to use aliases that provide shorter column names. Then
the view will replicate properly, and can be dumped and reloaded
without causing an error. To modify the definition, drop and
create the view again with
CREATE VIEW, or
replace the definition with
CREATE OR REPLACE
For problems that occur when reloading view definitions in dump
files, another workaround is to edit the dump file to modify its
CREATE VIEW statements. However,
this does not change the original view definitions, which may
cause problems for subsequent dump operations.