 LINEAR KEY partitioning for MySQL and MariaDB tables |
 |
LINEAR KEY partitioning is part of editing MySQL tables | |
|
|---|
 |
Creating a LINEAR KEY partition of MySQL or MariaDB tables:
|
| The partition assignment is calculated with a linear power of 2 algorithm.
BLOB or TEXT fields cannot be used for LINEAR 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 MySQL and MariaDB LINEAR KEY partitioning is a type of partitioning based on the range of values in a single partitioning column, similar to the KEY partitioning. Unlike KEY partitioning, LINEAR KEY partitioning uses a more efficient algorithm to evenly distribute data across partitions.
A single column is chosen as the partitioning key. The values in this column determine which partition a row belongs to. The number of partitions is determined implicitly by the storage engine based on the range of values in the partitioning key. The goal is to distribute the data evenly across the partitions. The partitioning algorithm used by MySQL and MariaDB LINEAR KEY partitioning is more efficient than that used by KEY partitioning used algorithm. Partitioning uses a linear hashing algorithm that minimizes the number of partitions that need to be accessed for a given query.
LINEAR KEY partitioning can improve query performance when using the partitioning key column in queries. The SQL server can quickly identify the relevant partitions based on the specified column values, enabling efficient data retrieval. The MySQL and MariaDB query optimizer performs partition pruning and eliminates irrelevant partitions from query execution based on the column values specified in the query. This optimization reduces the amount of data that needs to be scanned, thereby improving query performance.
Altering partitions can consume significant disk space and potentially cause downtime for large tables.
LINEAR KEY partitioning is a more efficient partitioning method than KEY partitioning. It enables improved query performance and efficient data distribution across partitions based on the values of a single column. |
| | More information about creating LINEAR KEY partitions in MySQL tables: and |
|
|
|
