Appendix D. Restrictions and Limits

The locking statements LOCK TABLES and UNLOCK TABLES.

ALTER VIEW.

LOAD DATA and LOAD TABLE.

SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be used in stored procedures, but not stored functions or triggers. Implication: You cannot use dynamic SQL within stored functions or triggers (where you construct dynamically statements as strings and then execute them).

In addition, SQL statements that are not permitted within prepared statements are also not permitted in stored routines. See Section 12.7, “SQL Syntax for Prepared Statements”, for a list of statements supported as prepared statements. Statements not listed there are not supported for SQL prepared statements and thus are also not supported for stored routines unless noted otherwise in Section 18.2, “Using Stored Routines (Procedures and Functions)”.

Inserts cannot be delayed. INSERT DELAYED syntax is accepted but the statement is handled as a normal INSERT.

Within all stored programs (stored procedures and functions, triggers, and events), the parser treats BEGIN [WORK] as the beginning of a BEGIN ... END block. Begin a transaction in this context with START TRANSACTION instead.

Statements that perform explicit or implicit commit or rollback. Support for these statements is not required by the SQL standard, which states that each DBMS vendor may decide whether to allow them.

Statements that return a result set. This includes SELECT statements that do not have an INTO var_list clause and other statements such as SHOW, EXPLAIN, and CHECK TABLE. A function can process a result set either with SELECT ... INTO var_list or by using a cursor and FETCH statements. See Section 12.8.3.3, “SELECT ... INTO Statement”.

FLUSH statements.

Stored functions cannot be used recursively.

Within a stored function or trigger, it is not permitted to modify a table that is already being used (for reading or writing) by the statement that invoked the function or trigger.

If you refer to a temporary table multiple times in a stored function under different aliases, a Can't reopen table: 'tbl_name' error occurs, even if the references occur in different statements within the function.

A stored function acquires table locks before executing, to avoid inconsistency in the binary log due to mismatch of the order in which statements execute and when they appear in the log. When statement-based binary logging is used, statements that invoke a function are recorded rather than the statements executed within the function. Consequently, stored functions that update the same underlying tables do not execute in parallel. In contrast, stored procedures do not acquire table-level locks. All statements executed within stored procedures are written to the binary log even for statement-based binary logging. See Section 18.6, “Binary Logging of Stored Programs”.

A local variable takes precedence over a routine parameter or table column

A routine parameter takes precedence over a table column

A local variable in an inner block takes precedence over a local variable in an outer block

Triggers currently are not activated by foreign key actions.

When using row-based replication, triggers on the slave are not activated by statements originating on the master. This does not apply when using statement-based replication. For more information, see Section 16.3.1.26, “Replication and Triggers”.

The RETURN statement is disallowed in triggers, which cannot return a value. To exit a trigger immediately, use the LEAVE statement.

Triggers are not allowed on tables in the mysql database.

Event names are handled in case-insensitive fashion. For example, this means that you cannot have two events in the same database with the names anEvent and AnEvent.

An event may not be created, altered, or dropped by a stored routine, trigger, or another event. An event also may not create, alter, or drop stored routines or triggers. (Bug#16409, Bug#18896)

Event timings using the intervals YEAR, QUARTER, MONTH, and YEAR_MONTH are resolved in months; those using any other interval are resolved in seconds. There is no way to cause events scheduled to occur at the same second to execute in a given order. In addition — due to rounding, the nature of threaded applications, and the fact that a nonzero length of time is required to create events and to signal their execution — events may be delayed by as much as 1 or 2 seconds. However, the time shown in the INFORMATION_SCHEMA.EVENTS table's LAST_EXECUTED column or the mysql.event table's last_executed column is always accurate to within one second of the actual event execution time. (See also Bug#16522.)

Each execution of the statements contained in the body of an event takes place in a new connection; thus, these statements has no effect in a given user session on the server's statement counts such as Com_select and Com_insert that are displayed by SHOW STATUS. However, such counts are updated in the global scope. (Bug#16422)

Events do not support times later than the end of the Unix Epoch; this is approximately the beginning of the year 2038. Such dates are specifically disallowed by the Event Scheduler. (Bug#16396)

References to stored functions, user-defined functions, and tables in the ON SCHEDULE clauses of CREATE EVENT and ALTER EVENT statements are not supported. These sorts of references are disallowed. (See Bug#22830 for more information.)

Generally speaking, statements which are not permitted in stored routines or in SQL prepared statements are also not allowed in the body of an event. For more information, see Section 12.7, “SQL Syntax for Prepared Statements”.

Stored routine definitions are kept in tables in the mysql system database using the MyISAM storage engine, and so do not participate in clustering.

The .TRN and .TRG files containing trigger definitions are not read by the NDB storage engine, and are not copied between Cluster nodes.

A subquery's outer statement can be any one of: SELECT, INSERT, UPDATE, DELETE, SET, or DO.

Subquery optimization for IN is not as effective as for the = operator or for the IN(value_list) operator.

A typical case for poor IN subquery performance is when the subquery returns a small number of rows but the outer query returns a large number of rows to be compared to the subquery result.

The problem is that, for a statement that uses an IN subquery, the optimizer rewrites it as a correlated subquery. Consider the following statement that uses an uncorrelated subquery:

SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2);

The optimizer rewrites the statement to a correlated subquery:

SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a);

If the inner and outer queries return M and N rows, respectively, the execution time becomes on the order of O(M×N), rather than O(M+N) as it would be for an uncorrelated subquery.

An implication is that an IN subquery can be much slower than a query written using an IN(value_list) operator that lists the same values that the subquery would return.

In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms:

DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);

Exception: The preceding prohibition does not apply if you are using a subquery for the modified table in the FROM clause. Example:

UPDATE t ... WHERE col = (SELECT * FROM (SELECT ... FROM t...) AS _t ...);

Here the prohibition does not apply because the result from a subquery in the FROM clause is stored as a temporary table, so the relevant rows in t have already been selected by the time the update to t takes place.

Row comparison operations are only partially supported:

In other words, for a subquery that returns rows of n-tuples, this is supported:

(val_1, ..., val_n) IN (subquery)

But this is not supported:

(val_1, ..., val_n) op {ALL|ANY|SOME} (subquery)

The reason for supporting row comparisons for IN but not for the others is that IN is implemented by rewriting it as a sequence of = comparisons and AND operations. This approach cannot be used for ALL, ANY, or SOME.

Row constructors are not well optimized. The following two expressions are equivalent, but only the second can be optimized:

(col1, col2, ...) = (val1, val2, ...)
col1 = val1 AND col2 = val2 AND ...

Subqueries in the FROM clause cannot be correlated subqueries. They are materialized (executed to produce a result set) before evaluating the outer query, so they cannot be evaluated per row of the outer query.

The optimizer is more mature for joins than for subqueries, so in many cases a statement that uses a subquery can be executed more efficiently if you rewrite it as a join.

An exception occurs for the case where an IN subquery can be rewritten as a SELECT DISTINCT join. Example:

SELECT col FROM t1 WHERE id_col IN (SELECT id_col2 FROM t2 WHERE condition);

That statement can be rewritten as follows:

SELECT DISTINCT col FROM t1, t2 WHERE t1.id_col = t2.id_col AND condition;

But in this case, the join requires an extra DISTINCT operation and is not more efficient than the subquery.

Possible future optimization: MySQL does not rewrite the join order for subquery evaluation. In some cases, a subquery could be executed more efficiently if MySQL rewrote it as a join. This would give the optimizer a chance to choose between more execution plans. For example, it could decide whether to read one table or the other first.

Example:

SELECT a FROM outer_table AS ot
WHERE a IN (SELECT a FROM inner_table AS it WHERE ot.b = it.b);

For that query, MySQL always scans outer_table first and then executes the subquery on inner_table for each row. If outer_table has a lot of rows and inner_table has few rows, the query probably will not be as fast as it could be.

The preceding query could be rewritten like this:

SELECT a FROM outer_table AS ot, inner_table AS it
WHERE ot.a = it.a AND ot.b = it.b;

In this case, we can scan the small table (inner_table) and look up rows in outer_table, which will be fast if there is an index on (ot.a,ot.b).

Possible future optimization: A correlated subquery is evaluated for each row of the outer query. A better approach is that if the outer row values do not change from the previous row, do not evaluate the subquery again. Instead, use its previous result.

Possible future optimization: A subquery in the FROM clause is evaluated by materializing the result into a temporary table, and this table does not use indexes. This does not allow the use of indexes in comparison with other tables in the query, although that might be useful.

Possible future optimization: If a subquery in the FROM clause resembles a view to which the merge algorithm can be applied, rewrite the query and apply the merge algorithm so that indexes can be used. The following statement contains such a subquery:

SELECT * FROM (SELECT * FROM t1 WHERE t1.t1_col)
         AS _t1, t2 WHERE t2.t2_col;

The statement can be rewritten as a join like this:

SELECT * FROM t1, t2 WHERE t1.t1_col AND t2.t2_col;

This type of rewriting would provide two benefits:

Possible future optimization: For IN, = ANY, <> ANY, = ALL, and <> ALL with uncorrelated subqueries, use an in-memory hash for a result or a temporary table with an index for larger results. Example:

SELECT a FROM big_table AS bt
WHERE non_key_field IN (SELECT non_key_field FROM table WHERE condition)

In this case, we could create a temporary table:

CREATE TABLE t (key (non_key_field))
(SELECT non_key_field FROM table WHERE condition)

Then, for each row in big_table, do a key lookup in t based on bt.non_key_field.

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).

If a statement prepared by PREPARE refers 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 EXECUTE statement is a random number, not the current date and time.

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.

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.

Identifiers are stored in mysql database tables (user, db, and so forth) using utf8, but identifiers can contain only characters in the Basic Multilingual Plane (BMP). Supplementary characters are not allowed in identifiers.

The ucs2 character sets has the following restrictions:

The REGEXP and RLIKE operators work in byte-wise fashion, so they are not multi-byte safe and may produce unexpected results with multi-byte character sets. In addition, these operators compare characters by their byte values and accented characters may not compare as equal even if a given collation treats them as equal.