 KEY partitioning for MySQL and MariaDB tables |
 |
KEY partitioning is part of editing MySQL tables | |
|
|---|
 |
Creating a KEY partition of MySQL or MariaDB tables:
|
| The partition assignment is calculated with a modulo algorithm (remainder in division).
BLOB or TEXT fields cannot be used for KEY partitioning.
| Partition by: | Choice of partitioning method. |
| Expression: | Table columns by which to partition.
Table columns can be selected (dropdown right).
| | Number of partitions: | Specifies how many partitions should be created. |
| Start: | Start the partition editor with the set parameters. |
|
|
Notice:
All table columns used for partitioning must be present in all UNIQUE indexes.
The program does this automatically and adds the columns to the indexes when partitioning.
In the table columns selection, columns that already exist in all UNIQUE indexes are displayed in blue color.
|
|
| Partition editor: |
 |
| Name: | Name of the partition. |
| Data Directory: | Number of data rows in partition. |
| Minimum number of lines: | MySQL partition management benchmark that specifies the minimum number of data rows in the partition.
| | Maximum number of lines: | MySQL partition management benchmark that specifies the maximum number of data rows in the partition.
| | TABLESPACE: | Selection of a defined TABLESPACE entry that contains information about the storage space of tables.
| | Comment: | Comment on the partition. |
|
For Data directory, Minimum number of lines, Maximum number of lines and TABLESPACE, the value contained can be copied to all other partitions by double-clicking on the input field.
|
|
 |
Create partitions. |
 |
Show the SQL statement to create the partitions. |
|
The KEY partitioning in MySQL and MariaDB is used to create partitions based on the values of one or more Uses columns that form a unique key. It allows to define partitions based on hash values of key columns. This can be useful when you want to partition data based on the values of a unique key, such as a primary key or combination of columns with unique values. One or more columns that form a unique key or combination of unique values are selected as the partitioning key.
MySQL and MariaDB calculate a hash value based on the partitioning key values. The hash value determines the partition to which a row of data belongs. The number of partitions should be determined based on the expected data size, system resources, and desired degree of parallelism. KEY partitioning can improve query performance when the partitioning key is used in queries. The SQL server can quickly determine the relevant partitions based on the hash value of the key columns, enabling efficient data access.
The query optimizer of MySQL and MariaDB performs partition pruning and eliminates irrelevant partitions from query execution based on hash value of key columns. This optimization reduces the amount of data that needs to be scanned, thereby improving query performance.
KEY partitioning provides performance benefits and flexibility when managing large tables with key-based queries. |
| | More information about creating KEY partitions in MySQL tables: and |
|
|
|
