Limitations of partitioning in MySQL 5.1

Some limitations of partitioning in MySQL 5.1 on one page.

  • All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.These rules also apply to existing nonpartitioned tables that you wish to partition.
  • Partitioning applies to all data and indexes of a table; you cannot partition only the data and not the indexes, or vice versa, nor can you partition only a portion of the table.
  • Prior to MySQL 5.1.43, it was not possible to partition a table by RANGE based on the value of a TIMESTAMP column by using the UNIX_TIMESTAMP() function.
  • When partitioning by LIST, you can match variable against only a list of integers values and some time NULL but not with char values. Never tried partition with string, it will not work.
  • If you explicitly define any subpartitions using SUBPARTITION option on any partition of a partitioned table, you must define them all.
  • Each SUBPARTITION clause must include (at a minimum) a name for the subpartition. Otherwise, you may set any desired option for the subpartition or allow it to assume its default setting for that option.
  • Stored procedures, stored functions, UDFs, declared variables or user variables are not permitted in partitioning expressions.
  • The bit operators |, &, ^, <<, >> and ~ are not permitted in partitioning expressions.
  • Tables employing user-defined partitioning do not preserve the SQL mode in effect at the time that they were created. 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.
  • The maximum possible number of partitions for a given table (that does not use the NDB storage engine) is 1024. This includes subpartitions.
  • The process executing a partitioning operation on a table takes a write lock on the table. Reads from such tables are relatively unaffected.
  • Partitioned tables do not support foreign keys.
  • Partitioned tables do not support FULLTEXT indexes.
  • 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. There is one exception to this restriction while using partitioning by 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.
  • A partitioning key may not be a subquery, even if that subquery resolves to an integer value or NULL.
  • Key caches are not supported for partitioned tables.
  • CHECK/OPTIMIZE/ANALYZE/REPAIR are not supported for partitioned tables prior to MySQL 5.1.27.
  • User-defined partitioning and the MERGE storage engine are not compatible. Tables using the MERGE storage engine cannot be partitioned. Partitioned tables cannot be merged.
  • When performing an upgrade, tables which are partitioned by KEY and which use any storage engine other than NDBCLUSTER must be dumped and reloaded.

More detailed information you can get from here: http://dev.mysql.com/doc/refman/5.1/en/partitioning-limitations.html

One thought on “Limitations of partitioning in MySQL 5.1”

Leave a Reply

Your email address will not be published. Required fields are marked *