Partitioned tables using the InnoDB storage engine do not support foreign keys. More specifically, this means that the following two statements are true:. No definition of an InnoDB table employing user-defined partitioning may contain foreign key references; no InnoDB table whose definition contains foreign key references may be partitioned.
No InnoDB table definition may contain a foreign key reference to a user-partitioned table; no InnoDB table with user-defined partitioning may contain columns referenced by foreign keys. The scope of the restrictions just listed includes all tables that use the InnoDB storage engine. It can be desirable in some cases see Section Be aware that, if your application uses REPLACE statements and you do this, the results of these statements can be drastically altered.
Spatial columns. Temporary tables. Temporary tables cannot be partitioned. Log tables. 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.
A partitioning key may not be a subquery, even if that subquery resolves to an integer value or 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.
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:.
Prior to MySQL 8. This permissive behavior is deprecated as of MySQL 8. Beginning with MySQL 8. For general information about partitioning tables by key, see Section Issues with subpartitions. Consider the table created by this statement:.
You can create a table having the same columns, partitioned by KEY , using a statement such as this one:. 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:.
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:.
You can employ these options for individual partitions or subpartitions of InnoDB tables. Add a comment. Active Oldest Votes. Rule of Thumb: Have partitions; no more. For real performance, use Summary tables. Improve this answer. Rick James Rick James I have posted a new question with the extra information you mentioned in your answer, as I believe it's beyond the scope of the question title.
Thanks Rick, the advice is much appreciated, I will implement all that you have mentioned, and inform you of the outcomes — Adam Copley.
I am looking forward to hearing the results. With this many 'changes' being recommended, it may take another iteration to get it working efficiently. Nuno Pereira Nuno Pereira 1 1 silver badge 11 11 bronze badges. Sign up or log in Sign up using Google. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Podcast Explaining the semiconductor shortage, and how it might end.
Does ES6 make JavaScript frameworks obsolete? Featured on Meta. Now live: A fully responsive profile. Linked 1. Other sources of information about user-defined partitioning in MySQL include the following:. This is the official discussion forum for those interested in or experimenting with MySQL Partitioning technology.
It features announcements and updates from MySQL developers and others. It is monitored by members of the Partitioning Development and Documentation Teams.
We encourage you to check here for links to blogs kept by those working with MySQL Partitioning, or to have your own blog added to those covered. However, for the latest partitioning bugfixes and feature additions, you can obtain the source from our GitHub repository.
If you have problems compiling a partitioning-enabled MySQL 5. InnoDB Cluster. Exchanging Partitions and Subpartitions with Tables. Maintenance of Partitions. Obtaining Information About Partitions. Restrictions and Limitations on Partitioning.
0コメント