 LIST COLUMNS partitioning for MySQL and MariaDB tables |
 |
LIST COLUMNS partitioning is part of editing MySQL tables | |
|
|---|
 |
Creating a LIST COLUMNS partitioning of MySQL or MariaDB tables:
|
| In addition to INTEGER, table fields of the type CHAR, VARCHAR, DATE, DATETIME can also be used for LIST 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 lists for individual partitions. |
| 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. |
|
The MySQL and MariaDB LIST COLUMNS partitioning is a method of partitioning a table based on a discrete set of column values. It can be used to define a list of column values for each partition, and each partition contains data that matches one of the specified column values. This type of partitioning is useful when you want to distribute data based on a set of predefined column values. 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 selected columns should have discrete and finite sets of values, such as enumerated values or category values. For each partition, the list of values for each column in the partitioning key is defined. Each list specifies the allowed values for the corresponding column.
The LIST COLUMNS partitioning ensures control over the definition of the column value lists and the management of the partitions. Partitions can be added, removed, and modified as needed.
LIST COLUMNS partitioning can improve query performance when using the partitioning key columns 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.
Adding, removing or changing partitions can consume significant disk space and can potentially result in downtime during the process for large tables.
LIST COLUMNS partitioning allows data to be partitioned based on a predefined set of column values. It offers improved query performance and enables efficient data management in large tables with discrete value-based column queries. |
| | More information about creating LIST COLUMNS partitions in MySQL tables: and |
|
|
|
