 LIST partitioning for MySQL and MariaDB tables |
 |
LIST partitioning is part of editing MySQL tables | |
|
|---|
 |
Creating a LIST partitioning of MySQL or MariaDB tables:
|
| Only INTEGER values can be used for LIST 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 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. |
|
|
| 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 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. |
|
MySQL and MariaDB LIST partitioning is a method of partitioning a table based on discrete values or a range of values in a column. Each partition contains data corresponding to the specified values in the partitioning key. This type of partitioning is useful when you want to distribute data based on specific values rather than ranges. A column or a range 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 column contains discrete values, such as categories or status types.
Instead of defining ranges, the discrete values or sets of values that define each partition are explicitly given. Each partition contains rows of data with the matching values in the partitioning key. The number of partitions should be determined based on the expected data size, system resources, and desired degree of parallelism. With the LIST partitioning the definition of the values and the management of the partitions can be controlled. Partitions can be added, removed, or modified as needed.
LIST partitioning can improve query performance when the partitioning key is used in queries. The SQL server can quickly identify the relevant partitions based on the specified values in the query, enabling efficient data retrieval. The MySQL and MariaDB query optimizer can perform partition sanitization by excluding partitions from consideration when executing a query based on the values specified in the query is performed. This optimization reduces the amount of data that needs to be scanned, thereby improving query performance.
LIST partitioning provides a way to partition data based on discrete values or sets of values in a column, provides improved query performance, and enables efficient data management in large tables with list-based queries.
Adding or removing partitions can consume significant disk space and potentially cause downtime during operations on large tables. |
| | More information about creating LIST partitions in MySQL tables: and |
|
|
|
