 RANGE partitioning for MySQL and MariaDB tables |
 |
RANGE partitioning is part of editing MySQL tables | |
|
|---|
 |
Creating a RANGE partitioning of MySQL or MariaDB tables:
|
| Only INTEGER values can be used for RANGE partitioning.
| Partitioning 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 column, you must use the UNIX_TIMESTAMP( column ) function.
|
| 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 column 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 column, the UNIX_TIMESTAMP( column ) 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 partitioning is a method of partitioning a table based on specified ranges of values in a column. Each partition contains data that falls within a specific range of values defined by the partitioning key. This type of partitioning is useful when you want to distribute data based on a specific sorting criterion, such as date ranges or numeric ranges.
A column or a set of columns is selected as the partitioning key. The values in these columns determine which partition a row of data belongs to. Typically, the selected column should have a range-based sorting criterion, such as date, timestamp, or numeric values. The ranges for each partition are explicitly defined when the table is created. Each range specifies the minimum and maximum values that determine the data contained in the partition. The number of partitions should be determined based on the expected data size, system resources, and desired degree of parallelism.
The RANGE partitioning allows control over the definition of the ranges and the management of the partitions. Partitions can be added or removed as needed and the partition ranges can be changed. Query performance can improve when the partitioning key is used in queries. The SQL server can quickly identify the relevant partitions based on the range conditions specified in the query, enabling efficient data retrieval. The query optimizer of MySQL and MariaDB can perform partition pruning by excluding partitions from consideration when executing a query based on range conditions. This optimization reduces the amount of data that needs to be scanned, thereby improving query performance.
Adding, removing or changes partitions can consumes significant disk space and, for large tables, may result in downtime during the operation. |
| | More information about creating RANGE partitions in MySQL tables: and |
|
|
|
