Skip navigation links
MySQL 5.1 with Maria Reference Manual :: 17 Partitioning :: 17.5 Restrictions and Limitations on Partitioning
17.5.1 Partitioning Keys, Primary Keys, and Unique Keys »
Section Navigation      [Toggle]

17.5. Restrictions and Limitations on Partitioning

[+/-]

17.5.1. Partitioning Keys, Primary Keys, and Unique Keys
17.5.2. Partitioning Limitations Relating to Storage Engines
17.5.3. Partitioning Limitations Relating to Functions

This section discusses current restrictions and limitations on MySQL partitioning support, as listed here:

  • Prohibited constructs.  Beginning with MySQL 5.1.12, the following constructs are not permitted in partitioning expressions:

    • Stored functions, stored procedures, UDFs, or plugins.

    • Declared variables or user variables.

    For a list of SQL functions which are permitted in partitioning expressions, see Section 17.5.3, “Partitioning Limitations Relating to Functions”.

  • Arithmetic and logical operators.  Use of the arithmetic operators +, –, and * is permitted in partitioning expressions. However, the result must be an integer value or NULL (except in the case of [LINEAR] KEY partitioning, as discussed elswhere in this chapter — see Section 17.2, “Partition Types”, for more information).

    Beginning with MySQL 5.1.23, the DIV operator is also supported, and the / operator is disallowed. (Bug#30188, Bug#33182)

    Beginning with MySQL 5.1.12, the bit operators |, &, ^, <<, >>, and ~ are not permitted in partitioning expressions.

  • 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 in Section 5.1.8, “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.

    Examples.  The following examples illustrate some changes in behavior of partitioned tables due to a change in the server SQL mode:

    1. Error handling.  Suppose you create a partitioned table whose partitioning expression is one such as column DIV 0 or column MOD 0, as shown here: 

      mysql> CREATE TABLE tn (c1 INT)
    ->     PARTITION BY LIST(1 DIV c1) (
    ->       PARTITION p0 VALUES IN (NULL),
    ->       PARTITION p1 VALUES IN (1)
    -> );
Query OK, 0 rows affected (0.05 sec)

      The default behavior for MySQL is to return NULL for the result of a division by zero, without producing any errors: 

      mysql> SELECT @@SQL_MODE;
+------------+
| @@SQL_MODE |
+------------+
|            |
+------------+
1 row in set (0.00 sec)


mysql> INSERT INTO tn VALUES (NULL), (0), (1);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

      However, changing the server SQL mode to treat division by zero as an error and to enforce strict error handling causes the same INSERT statement to fail, as shown here: 

      mysql> SET SQL_MODE='STRICT_ALL_TABLES,ERROR_FOR_DIVISION_BY_ZERO';
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO tn VALUES (NULL), (0), (1);
ERROR 1365 (22012): Division by 0

    2. Table accessibility.  Sometimes a change in the server SQL mode can make partitioned tables unusable. The following CREATE TABLE statement can be executed successfully only if the NO_UNSIGNED_SUBTRACTION mode is in effect: 

      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 NO_UNSIGNED_SUBTRACTION server 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

  • Performance considerations. 

    • File system operations.  Partitioning and repartitioning operations (such as ALTER TABLE with PARTITION BY ..., REORGANIZE PARTITIONS, 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_support is enabled and that open_files_limit is set properly. For partitioned tables using the MyISAM storage engine, increasing myisam_max_sort_file_size may improve performance; partitioning and repartitioning operations involving InnoDB tables may be made more efficient by enabling innodb_file_per_table.

    • Table locks.  The process executing a partitioning operation on a table takes a write lock on the table. Reads from such tables are relatively unaffected; pending INSERT and UPDATE operations are performed as soon as the partitioning operation has completed.

    • Storage engine.  Partitioning operations, queries, and update operations generally tend to be faster with MyISAM tables than with InnoDB or NDB tables.

    • Use of indexes and 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 17.4, “Partition Pruning”, for more information.

    • Performance with LOAD DATA.  Prior to MySQL 5.1.23, LOAD DATA performed very poorly when importing into partitioned tables. The statement now uses buffering to improve performance; however, the buffer uses 130 KB memory per partition to achieve this. (Bug#26527)

  • Maximum number of partitions.  The maximum number of partitions possible for a given table is 1024. This includes subpartitions.

    If, when creating tables with a very large number of partitions (but which is less than the maximum stated previously), you encounter an error message such as Got error 24 from storage engine, this means that you may need to increase the value of the open_files_limit system variable. See Section B.1.2.18, “'File' Not Found and Similar Errors”.

  • Foreign keys not supported.  Partitioned tables do not support foreign keys. This means that:

    1. Definitions of tables employing user-defined partitioning may not contain foreign key references to other tables.

    2. No table definition may contain a foreign key reference to a partitioned table.

    The scope of these restrictions includes tables that use the InnoDB storage engine.

  • ALTER TABLE ... ORDER BY.  An ALTER TABLE ... ORDER BY column statement run against a partitioned table causes ordering of rows only within each partition.

  • FULLTEXT indexes.  Partitioned tables do not support FULLTEXT indexes. This includes partitioned tables employing the MyISAM storage engine.

  • Spatial columns.  Columns with spatial data types such as POINT or GEOMETRY cannot be used in partitioned tables.

  • Temporary tables.  As of MySQL 5.1.8, temporary tables cannot be partitioned. (Bug#17497)

  • Log tables.  Beginning with MySQL 5.1.20, it is no longer possible to partition the log tables; beginning with that version, an ALTER TABLE ... PARTITION BY ... statement on such a table fails with an error. (Bug#27816)

  • Data type of partitioning key.  A partitioning key must be either an integer column or an expression that resolves to an integer. The column or expression value may also be NULL. (See Section 17.2.6, “How MySQL Partitioning Handles NULL”.)

    The lone exception to this restriction occurs when partitioning by [LINEAR] KEY, where it is possible to use columns of other types as partitioning keys, because MySQL's internal key-hashing functions produce the correct data type from these types. For example, the following CREATE TABLE statement is valid: 

    CREATE TABLE tkc (c1 CHAR)
PARTITION BY KEY(c1)
PARTITIONS 4;

    This exception does not apply to BLOB or TEXT column types.

  • Subqueries.  A partitioning key may not be a subquery, even if that subquery resolves to an integer value or NULL.

  • Subpartitions.  Subpartitions are limited to HASH or KEY partitioning. HASH and KEY partitions cannot be subpartitioned.

  • Key caches not supported.  Key caches are not supported for partitioned tables. The CACHE INDEX and LOAD INDEX INTO CACHE statements, when you attempt to use them on tables having user-defined partitioning, fail with the errors The storage engine for the table doesn't support assign_to_keycache and The storage engine for the table doesn't support preload_keys, respectively.

  • DELAYED option not supported.  Use of INSERT DELAYED to insert rows into a partitioned table is not supported. Beginning with MySQL 5.1.23, attempting to do so fails with an error. (Bug#31210)

  • DATA DIRECTORY and INDEX DIRECTORY options.  DATA DIRECTORY and INDEX DIRECTORY are subject to the following restrictions when used with partitioned tables:

    • Beginning with MySQL 5.1.23, table-level DATA DIRECTORY and INDEX DIRECTORY options are ignored. (Bug#32091)

    • On Windows, the DATA DIRECTORY and INDEX DIRECTORY options are not supported for individual partitions or subpartitions (Bug#30459).

  • Repairing and rebuilding partitioned tables.  The statements CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE, and REPAIR TABLE are supported for partitioned tables beginning with MySQL 5.1.27. (See Bug#20129.) mysqlcheck and myisamchk are not supported with partitioned tables.

    In addition, you can use ALTER TABLE ... REBUILD PARTITION to rebuild one or more partitions of a partitioned table; ALTER TABLE ... REORGANIZE PARTITION also causes partitions to be rebuilt. Both of these statements were added in MySQL 5.1.5. See Section 12.1.7, “ALTER TABLE Syntax”, for more information about these two statements.