The maximum number of tables that can be referenced in the definition of a view is 61.

View processing is not optimized:

There is a general principle that you cannot modify a table and select from the same table in a subquery. SeeChapter 5,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 ASSELECT * 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, youcan select from the table in the view subquery and still modify that table in the outer query. In this case, the view is 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 specifyingALGORITHM = TEMPTABLE in the view definition.)

You can useDROP TABLE orALTER TABLE to drop or alter a table that is used in a view definition. No warning results from theDROP orALTER operation, even though this invalidates the view. Instead, an error occurs later, when the view is used.CHECK TABLE can be used to check for views that have been invalidated byDROP orALTER operations.

With regard to view updatability, the overall goal for views is that if any view is theoretically updatable, it should be updatable in practice. Many theoretically updatable views can be updated now, but limitations still exist. For details, seeUpdatable and Insertable Views.

There exists a shortcoming with the current implementation of views. If a user is granted the basic privileges necessary to create a view (theCREATE VIEW andSELECT privileges), that user cannot callSHOW CREATE VIEW on that object unless the user is also granted theSHOW VIEW privilege.

That shortcoming can lead to problems backing up a database withmysqldump, 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 theSHOW VIEW privilege to users who are grantedCREATE VIEW, since MySQL doesn't grant it implicitly when views are created.

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 anASalias_name clause for each column. If a column is created from an expression, the default alias is the expression text, which can be quite long. Aliases for column names inCREATE 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 ofSHOW CREATE VIEW fail if any column alias exceeds 64 characters. This can cause problems in the following circumstances for views with too-long aliases:

A workaround for either problem is to modify each problematic view definition to use aliases that provide shorter column names. Then the view replicates properly, and can be dumped and reloaded without causing an error. To modify the definition, drop and create the view again withDROP VIEW andCREATE VIEW, or replace the definition withCREATE OR REPLACE VIEW.

For problems that occur when reloading view definitions in dump files, another workaround is to edit the dump file to modify itsCREATE VIEW statements. However, this does not change the original view definitions, which may cause problems for subsequent dump operations.