 HASH partitioning for MySQL and MariaDB tables |
 |
HASH partitioning is part of editing MySQL tables | |
|
|---|
 |
Creating a HASH partition of MySQL or MariaDB tables:
|
| The partition assignment is calculated with a modulo algorithm (remainder in division).
Only INTEGER values can be used for HASH partitioning.
| Partition by: | Choice of partitioning method. |
| Expression: | Expression or table column by which to partition.
You can select functions (dropdown left) and table columns (dropdown right).
Depending on the selected function, suitable table columns are made available.
If you want to partition by a TIMESTAMP field, you must use the UNIX_TIMESTAMP( field ) function. |
| 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. |
|
MySQL and MariaDB HASH partitioning is a method of partitioning a table based on a hash function that targets a specific column is applied. It distributes rows of data across multiple partitions based on the result of the hash function rather than based on a range or list of values. This type of partitioning is useful when you want data to be evenly distributed across partitions without specific sorting criteria, and can help improve query performance on tables and enable efficient data management in large database tables. The hash function converts the value of the column into a hash value that determines the partition to which the row belongs.
A column or a range of columns must be selected as the partitioning key. The selected columns must be of a hashable data type, such as integer or character. Ideally, the columns selected should have a good distribution of values to achieve even data distribution across the partitions. The number of partitions is explicitly specified when the table is created. The number of partitions should be determined based on the expected data size, system resources, and desired degree of parallelism.
MySQL and MariaDB automatically calculate the partition for each row based on the hash function. This simplifies partition management compared to other partitioning methods. Query performance can be further improved by using the partitioning key in queries. SQL Server can determine the target partitions for a query using the hash value calculated from the partitioning key, enabling efficient data retrieval.
Adding or removing partitions can be more time consuming compared to range or list partitioning. Additionally, hash partitioning does not lend itself to range-based queries or ordered data retrieval. |
| | More information about creating HASH partitions in MySQL tables: and |
|
|
|
