 |
The partition management is part of the table editing | |
|
|---|
 |
MySQL Partitioning - Module for managing partitions of MySQL and MariaDB tables:
|
| Service functions can be executed on the partitions.
| Analyze: | Analyze partition. |
| Verifying: | Verify partition. |
| Optimize: | Optimize partition. |
| Rebuild: | Rebuild partition. |
| Repair: | Repair partition. |
| Empty: | Partition is emptied. Attention! All data on the partition will be deleted. |
|
|
All functions can be performed on individual, selected or all partitions.
|
|
| Table columns: |
| Name: | Name of the partition. |
| Method: | Method of partitioning. |
| Expression: | Expression or table field to be used for partitioning. |
| Description: | Description / condition for expression. |
| Lines of data: | Number of data rows in partition. |
| Data storage: | Used dataspace of the partition.
| | Index memory: | Index space used by the partition.
| | Comment: | Comment for partition. |
|
 |
Displays the partition editing form. |
 |
View service features for selected partitions. |
 |
Displays form to add partitions. |
 |
Displays form to reduce the number of partitions. |
 |
Unpartitions the MySQL table. |
|
| Example of partitioning with sub-partitions: |
 |
Example with partitioning using RANGE and HASH for sub-partitions:
|
| Additional service functions are available for certain types of partitioning:
| Split: | Launches partition splitting form. |
| Delete: | Partition will be deleted. Warning! All data on the partition will be lost. |
| Merge / Reorganize: | Selected partitions can be merged or reorganized. |
|
|
 |
|
Labeled menu by double-clicking title bar.
|
MySQL and MariaDB partitioning is a feature that allows large database tables to be divided into smaller, more manageable parts called partitions. Each partition acts as a separate logical entity with its own storage engine file and index structure. Partitioning distributes data across multiple physical storage units based on a partitioning key. This key determines which partition each row of data belongs to.
One of the main benefits of partitioning is the ability to clean up partitions during query execution. When a query includes a partition key condition, MySQL and MariaDB can determine which partitions need to be scanned, resulting in improved query performance. Partitioning can simplify data maintenance operations, such as adding or removing data. For example, new partitions can be added or old partitions can be deleted without these actions affecting the entire table.
Each partition has its own index structure, allowing for more efficient index scans and improved query performance within specific partitions. However, there are some limitations: For example, the inability to create foreign key constraints that refer to partitioned tables. Additionally, changing the partitioning scheme of an existing table can be a time-consuming process.
Partitioning in MySQL and MariaDB can improve query performance, simplify data maintenance, and allow for better maintainability of large database tables. However, it requires careful planning and consideration of data distribution and query patterns to reap the full benefits. |
