This section discusses current restrictions and limitations on MySQL partitioning support.
Prohibited constructs. The following constructs are not permitted in partitioning expressions:
Stored procedures, stored functions, loadable functions, or plugins.
Declared variables or user variables.
For a list of SQL functions which are permitted in partitioning expressions, see Section 24.6.3, “Partitioning Limitations Relating to Functions”.
Arithmetic and logical operators.
Use of the arithmetic operators
* is permitted in
partitioning expressions. However, the result must be an integer
NULL (except in the case of
[LINEAR] KEY partitioning, as discussed
elsewhere in this chapter; see
Section 24.2, “Partitioning Types”, for more information).
Server SQL mode. Tables employing user-defined partitioning do not preserve the SQL mode in effect at the time that they were created. As discussed elsewhere in this Manual (see Section 5.1.11, “Server SQL Modes”), the results of many MySQL functions and operators may change according to the server SQL mode. Therefore, a change in the SQL mode at any time after the creation of partitioned tables may lead to major changes in the behavior of such tables, and could easily lead to corruption or loss of data. For these reasons, it is strongly recommended that you never change the server SQL mode after creating partitioned tables.
For one such change in the server SQL mode making a partitioned
tables unusable, consider the following
CREATE TABLE statement, which can
be executed successfully only if the
NO_UNSIGNED_SUBTRACTION mode is
mysql> SELECT @@sql_mode; +------------+ | @@sql_mode | +------------+ | | +------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE tu (c1 BIGINT UNSIGNED) -> PARTITION BY RANGE(c1 - 10) ( -> PARTITION p0 VALUES LESS THAN (-5), -> PARTITION p1 VALUES LESS THAN (0), -> PARTITION p2 VALUES LESS THAN (5), -> PARTITION p3 VALUES LESS THAN (10), -> PARTITION p4 VALUES LESS THAN (MAXVALUE) -> ); ERROR 1563 (HY000): Partition constant is out of partition function domain mysql> SET sql_mode='NO_UNSIGNED_SUBTRACTION'; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@sql_mode; +-------------------------+ | @@sql_mode | +-------------------------+ | NO_UNSIGNED_SUBTRACTION | +-------------------------+ 1 row in set (0.00 sec) mysql> CREATE TABLE tu (c1 BIGINT UNSIGNED) -> PARTITION BY RANGE(c1 - 10) ( -> PARTITION p0 VALUES LESS THAN (-5), -> PARTITION p1 VALUES LESS THAN (0), -> PARTITION p2 VALUES LESS THAN (5), -> PARTITION p3 VALUES LESS THAN (10), -> PARTITION p4 VALUES LESS THAN (MAXVALUE) -> ); Query OK, 0 rows affected (0.05 sec)
If you remove the
SQL mode after creating
tu, you may no longer
be able to access this table:
mysql> SET sql_mode=''; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM tu; ERROR 1563 (HY000): Partition constant is out of partition function domain mysql> INSERT INTO tu VALUES (20); ERROR 1563 (HY000): Partition constant is out of partition function domain
See also Section 5.1.11, “Server SQL Modes”.
Server SQL modes also impact replication of partitioned tables. Disparate SQL modes on source and replica can lead to partitioning expressions being evaluated differently; this can cause the distribution of data among partitions to be different in the source's and replica's copies of a given table, and may even cause inserts into partitioned tables that succeed on the source to fail on the replica. For best results, you should always use the same server SQL mode on the source and on the replica.
Performance considerations. Some effects of partitioning operations on performance are given in the following list:
File system operations. Partitioning and repartitioning operations (such as
PARTITION BY ...,
REORGANIZE PARTITION, or
REMOVE PARTITIONING) depend on file system operations for their implementation. This means that the speed of these operations is affected by such factors as file system type and characteristics, disk speed, swap space, file handling efficiency of the operating system, and MySQL server options and variables that relate to file handling. In particular, you should make sure that
large_files_supportis enabled and that
open_files_limitis set properly. Partitioning and repartitioning operations involving
InnoDBtables may be made more efficient by enabling
See also Maximum number of partitions.
Table locks. Generally, the process executing a partitioning operation on a table takes a write lock on the table. Reads from such tables are relatively unaffected; pending
UPDATEoperations are performed as soon as the partitioning operation has completed. For
InnoDB-specific exceptions to this limitation, see Partitioning Operations.
Indexes; partition pruning. As with nonpartitioned tables, proper use of indexes can speed up queries on partitioned tables significantly. In addition, designing partitioned tables and queries on these tables to take advantage of partition pruning can improve performance dramatically. See Section 24.4, “Partition Pruning”, for more information.
Index condition pushdown is supported for partitioned tables. See Section 22.214.171.124, “Index Condition Pushdown Optimization”.
Performance with LOAD DATA. In MySQL 8.2,
LOAD DATAuses buffering to improve performance. You should be aware that the buffer uses 130 KB memory per partition to achieve this.
Maximum number of partitions.
The maximum possible number of partitions for a given table not
NDB storage engine is
8192. This number includes subpartitions.
The maximum possible number of user-defined partitions for a table
NDB storage engine is
determined according to the version of the NDB Cluster software
being used, the number of data nodes, and other factors. See
NDB and user-defined partitioning,
for more information.
If, when creating tables with a large number of partitions (but
less than the maximum), you encounter an error message such as
Got error ... from storage engine: Out of resources
when opening file, you may be able to address the
issue by increasing the value of the
open_files_limit system variable.
However, this is dependent on the operating system, and may not be
possible or advisable on all platforms; see
Section B.3.2.16, “File Not Found and Similar Errors”, for more information.
In some cases, using large numbers (hundreds) of partitions may
also not be advisable due to other concerns, so using more
partitions does not automatically lead to better results.
See also File system operations.
Foreign keys not supported for partitioned InnoDB tables.
Partitioned tables using the
storage engine do not support foreign keys. More specifically,
this means that the following two statements are true:
No definition of an
InnoDBtable employing user-defined partitioning may contain foreign key references; no
InnoDBtable whose definition contains foreign key references may be partitioned.
InnoDBtable definition may contain a foreign key reference to a user-partitioned table; no
InnoDBtable with user-defined partitioning may contain columns referenced by foreign keys.
The scope of the restrictions just listed includes all tables that
InnoDB storage engine.
statements that would result in tables violating these
restrictions are not allowed.
ALTER TABLE ... ORDER BY.
ALTER TABLE ... ORDER BY
against a partitioned table causes ordering of rows only within
ADD COLUMN ... ALGORITHM=INSTANT.
Once you perform
ALTER TABLE ... ADD
COLUMN ... ALGORITHM=INSTANT on a partitioned table,
it is no longer possible to exchange partitions with this table.
Effects on REPLACE statements by modification of primary keys.
It can be desirable in some cases (see
Section 24.6.1, “Partitioning Keys, Primary Keys, and Unique Keys”)
to modify a table's primary key. Be aware that, if your
statements and you do this, the results of these statements can
be drastically altered. See Section 13.2.12, “REPLACE Statement”, for more
information and an example.
Columns with spatial data types such as
GEOMETRY cannot be used in partitioned
It is not possible to partition the log tables; an
TABLE ... PARTITION BY ... statement on such a table
fails with an error.
Data type of partitioning key.
A partitioning key must be either an integer column or an
expression that resolves to an integer. Expressions employing
ENUM columns cannot be used. The
column or expression value may also be
see Section 24.2.7, “How MySQL Partitioning Handles NULL”.
There are two exceptions to this restriction:
When partitioning by [
KEY, it is possible to use columns of any valid MySQL data type other than
BLOBas partitioning keys, because the internal key-hashing functions produce the correct data type from these types. For example, the following two
CREATE TABLEstatements are valid:
CREATE TABLE tkc (c1 CHAR) PARTITION BY KEY(c1) PARTITIONS 4; CREATE TABLE tke ( c1 ENUM('red', 'orange', 'yellow', 'green', 'blue', 'indigo', 'violet') ) PARTITION BY LINEAR KEY(c1) PARTITIONS 6;
CREATE TABLE rc (c1 INT, c2 DATE) PARTITION BY RANGE COLUMNS(c2) ( PARTITION p0 VALUES LESS THAN('1990-01-01'), PARTITION p1 VALUES LESS THAN('1995-01-01'), PARTITION p2 VALUES LESS THAN('2000-01-01'), PARTITION p3 VALUES LESS THAN('2005-01-01'), PARTITION p4 VALUES LESS THAN(MAXVALUE) ); CREATE TABLE lc (c1 INT, c2 CHAR(1)) PARTITION BY LIST COLUMNS(c2) ( PARTITION p0 VALUES IN('a', 'd', 'g', 'j', 'm', 'p', 's', 'v', 'y'), PARTITION p1 VALUES IN('b', 'e', 'h', 'k', 'n', 'q', 't', 'w', 'z'), PARTITION p2 VALUES IN('c', 'f', 'i', 'l', 'o', 'r', 'u', 'x', NULL) );
Column index prefixes not supported for key partitioning.
When creating a table that is partitioned by key, any columns in
the partitioning key which use column prefixes are not used in
the table's partitioning function. Consider the following
CREATE TABLE statement, which has
VARCHAR columns, and whose
primary key uses all three columns and specifies prefixes for
two of them:
CREATE TABLE t1 ( a VARCHAR(10000), b VARCHAR(25), c VARCHAR(10), PRIMARY KEY (a(10), b, c(2)) ) PARTITION BY KEY() PARTITIONS 2;
This statement is accepted, but the resulting table is actually
created as if you had issued the following statement, using only
the primary key column which does not include a prefix (column
b) for the partitioning key:
CREATE TABLE t1 ( a VARCHAR(10000), b VARCHAR(25), c VARCHAR(10), PRIMARY KEY (a(10), b, c(2)) ) PARTITION BY KEY(b) PARTITIONS 2;
This permissive behavior is deprecated (and is subject to removal in a future version of MySQL). Using one or more columns having a prefix in the partitioning key results in a warning for each such column, as shown here:
mysql> CREATE TABLE t1 ( -> a VARCHAR(10000), -> b VARCHAR(25), -> c VARCHAR(10), -> PRIMARY KEY (a(10), b, c(2)) -> ) PARTITION BY KEY() PARTITIONS 2; Query OK, 0 rows affected, 2 warnings (1.25 sec) mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Warning Code: 1681 Message: Column 'test.t1.a' having prefix key part 'a(10)' is ignored by the partitioning function. Use of prefixed columns in the PARTITION BY KEY() clause is deprecated and will be removed in a future release. *************************** 2. row *************************** Level: Warning Code: 1681 Message: Column 'test.t1.c' having prefix key part 'c(2)' is ignored by the partitioning function. Use of prefixed columns in the PARTITION BY KEY() clause is deprecated and will be removed in a future release. 2 rows in set (0.00 sec)
This includes cases in which the columns used in the partitioning
function are defined implicitly as those in the table's
primary key by employing an empty
If all columns specified for the partitioning key employ prefixes,
CREATE TABLE statement used fails with an
error message that identifies the issue correctly:
mysql> CREATE TABLE t1 ( -> a VARCHAR(10000), -> b VARCHAR(25), -> c VARCHAR(10), -> PRIMARY KEY (a(10), b(5), c(2)) -> ) PARTITION BY KEY() PARTITIONS 2; ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function (prefixed columns are not considered).
For general information about partitioning tables by key, see Section 24.2.5, “KEY Partitioning”.
SUBPARTITION BY KEY requires that the
subpartitioning column or columns be specified explicitly, unlike
the case with
PARTITION BY KEY, where it can be
omitted (in which case the table's primary key column is used
by default). Consider the table created by this statement:
CREATE TABLE ts ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) );
You can create a table having the same columns, partitioned by
KEY, using a statement such as this one:
CREATE TABLE ts ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) ) PARTITION BY KEY() PARTITIONS 4;
The previous statement is treated as though it had been written like this, with the table's primary key column used as the partitioning column:
CREATE TABLE ts ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) ) PARTITION BY KEY(id) PARTITIONS 4;
However, the following statement that attempts to create a subpartitioned table using the default column as the subpartitioning column fails, and the column must be specified for the statement to succeed, as shown here:
mysql> CREATE TABLE ts ( -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> name VARCHAR(30) -> ) -> PARTITION BY RANGE(id) -> SUBPARTITION BY KEY() -> SUBPARTITIONS 4 -> ( -> PARTITION p0 VALUES LESS THAN (100), -> PARTITION p1 VALUES LESS THAN (MAXVALUE) -> ); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') mysql> CREATE TABLE ts ( -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -> name VARCHAR(30) -> ) -> PARTITION BY RANGE(id) -> SUBPARTITION BY KEY(id) -> SUBPARTITIONS 4 -> ( -> PARTITION p0 VALUES LESS THAN (100), -> PARTITION p1 VALUES LESS THAN (MAXVALUE) -> ); Query OK, 0 rows affected (0.07 sec)
This is a known issue (see Bug #51470).
DATA DIRECTORY and INDEX DIRECTORY options.
DATA DIRECTORY and
DIRECTORY options are ignored (see Bug #32091). You
can employ these options for individual partitions or
InnoDB tables. The
directory specified in a
clause must be known to
more information, see
Using the DATA DIRECTORY Clause.
In addition, you can use
ALTER TABLE ... REBUILD
PARTITION to rebuild one or more partitions of a
ALTER TABLE ... REORGANIZE
PARTITION also causes partitions to be rebuilt. See
Section 13.1.9, “ALTER TABLE Statement”, for more information about these
TRUNCATE operations are supported with
Section 126.96.36.199, “ALTER TABLE Partition Operations”.
File name delimiters for partitions and subpartitions.
Table partition and subpartition file names include generated
delimiters such as
#SP#. The lettercase of such delimiters can
vary and should not be depended upon.