RANGE COLUMNS partitioning for MySQL and MariaDB tables |
|
RANGE COLUMNS partitioning is part of editing MySQL tables | |
|
|
Creating a RANGE COLUMNS partitioning of MySQL or MariaDB tables:
|
| Only table fields with INTEGER values can be used for RANGE COLUMNS partitioning. Multiple fields can be specified.
Partitioning by: | Choice of partitioning method. |
Table columns: | Expression or 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. |
|
|
| Subpartitions can be created:
Subpartitions by: | Select partitioning method (HASH, LINEAR HASH, KEY, LINEAR KEY). |
Expression: | Expression or table field by which to partition.
You can select functions (dropdown on the left only HASH, LINEAR HASH) and table columns (dropdown on the right). .
Depending on the selected function, suitable table columns are made available.
If a HASH partitioning is to be partitioned according to a TIMESTAMP field, the UNIX_TIMESTAMP( field ) function must be used.
|
Number of subpartitions: | Specification of how many sub-partitions should be created. |
|
|
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 column selection, columns that already exist in all UNIQUE indices are displayed in blue color.
|
|
Partition editor: |
|
Name: | Name of the partition. |
Values: | Value assignment for individual partitions.
Start value: | Start value for automated value assignment. |
Increment: | Increment for automated value assignment. |
|
Subpartition: | Name of the sub-partition. |
Data Directory: | Number of data rows in partition. |
Minimum number of rows: | MySQL partition management benchmark that specifies the minimum number of data rows in the partition.
| Maximum number of rows: | 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 rows, maximum number of rows and TABLESPACE, the value contained can be copied to all other partitions by double-clicking on the input field.
|
|
|
Assign values to partitions. |
|
|
Create partitions. |
|
Show the SQL statement to create the partitions. |
|
MySQL and MariaDB RANGE COLUMNS partitioning is a method of partitioning a table based on a combination of column values. You can use it to define ranges for multiple columns, and each partition will contain data that falls within the specified column value ranges. This type of partitioning is useful when you want to distribute data based on a combination of column values rather than a single column or range-based criterion. A set of columns is chosen as the partitioning key. The values in these columns determine which partition a row of data belongs to. Typically, the columns you select should have range-based sorting criteria, such as numeric values or date and time values.
For each partition, the ranges of values for each column in the partitioning key are defined. Each range specifies the minimum and maximum values for the corresponding column. The number of partitions should be determined based on the expected data size, system resources, and desired degree of parallelism. The RANGE COLUMNS partitioning ensures control over the definition of the column value ranges and the management of the partitions. Partitions can be added, removed, or modified as needed.
RANGE COLUMNS partitioning can improve query performance when using the partitioning key columns in queries. MySQL and MariaDB can quickly determine the relevant partitions based on the range conditions specified for the column values, enabling efficient data access. The MySQL and MariaDB query optimizer performs partition pruning and eliminates irrelevant partitions from query execution based on the scope conditions specified in the query. This optimization reduces the amount of data that needs to be scanned, thereby improving query performance. It offers improved query performance and enables efficient data management in large tables with range-based column queries.
Adding, removing or changing partitions can consume significant disk space and can potentially result in downtime during the process for large tables. |
| More information about creating RANGE COLUMNS partitions in MySQL tables: and |
|
|
|