View restrictions in the MySQL database

The concept of view handling functionality is not optimized:

• Indexes cannot be created on the view.

• Indexes can be used for views that are processed using the merge algorithm. However, views that are processed using temporary table algorithms cannot take advantage of the benefits of indexing on their base tables, although they can be used during the generation of temporary tables.

Subqueries cannot be used in the FROM clause of the view. The restrictions will be relaxed in the future.

There is a general principle that you cannot change a table and select it in the same table in a subquery. See I.3 section, "Restrictions on subqueries."

The same principle applies if you select a view from a table and then select it from a view, as well if you select a view from a table in a subquery and use the merge algorithm to evaluate the view. For 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 a table in the view subquery and still be able to change the table in the external query. In that case, the view will be materialized, so you can't actually select from the table in the subquery and change it at the same time (this is another reason you intend to force MySQL to use the temporary table algorithm by specifying the algorithm = temptable keyword in the view definition).

You can use DROP table or ALTER TABLE to discard or change the table that is used in the view definition (it can be a view invalidation), and discarding or changing the operation does not cause an alarm. However, there will be an error when you use the view later.

The view definition is "Frozen" by a specific statement:

• If a prepare-preprocessed statement references a view, the view content that you see at each subsequent execution of the statement is the same as the content of the preprocessed view. The same is true even after the statement preprocessing is complete and the view definition is changed before the statement is executed. For example:

· CREATE VIEW v as SELECT 1;

· PREPARE s from ' SELECT * from V ';

· ALTER VIEW v as SELECT 2;

· EXECUTE s;

The result returned by the EXECUTE statement is 1, not 2.