 LINEAR HASH partitioning for MySQL and MariaDB tables |
 |
LINEAR HASH partitioning is part of editing MySQL tables | |
|
|---|
 |
Creating a LINEAR HASH partition of MySQL or MariaDB tables:
|
| The partition assignment is calculated with a linear power of 2 algorithm.
Only INTEGER values can be used for LINEAR 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. |
|
The LINEAR HASH partitioning of MySQL and MariaDB is a type of partitioning that partitions data using a linear hash Algorithm evenly distributed across partitions. It is similar to HASH partitioning, but has some optimizations that can improve performance.
A single table column is chosen as the partitioning key. The values in this column determine which partition a row belongs to. The partitioning algorithm used minimizes the number of partitions that must be accessed for a given query. This algorithm helps distribute data evenly across partitions, making it a good choice for high-volume applications.
LINEAR HASH 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. |
| | More information about creating LINEAR HASH partitions in MySQL tables: and |
|
|
|
