Online Documentation for SQL Manager for MySQL

Partitions

Partitioning states for dividing big tables into logical parts due to the selected criteria.

Partitions are available for server version 5.1 and higher. This feature cannot be applied to federated tables.

Use the respective tab of Table Editor to define Partitioning settings.

 

 

Partition type

Use the drop-down list to define data division method.

 

• Range

A table that is partitioned by range is partitioned in such a way that each partition contains rows for which the partitioning expression value lies within a given range.

Example:

PARTITION BY RANGE (store_id) (

PARTITION p0 VALUES LESS THAN (10),

• List

In list partitioning, each partition is defined and selected based on the membership of a column value in one of a set of value lists.

Example:

PARTITION BY LIST(store_id) (

PARTITION pNorth VALUES IN (3,5,6,9,17),

• Hash

Partitioning by HASH is used primarily to ensure an even distribution of data among a predetermined number of partitions. You need only specify a column value or expression based on a column value to be hashed and the number of partitions into which the partitioned table is to be divided.

• Linear hash

Linear hashing differs from regular hashing in that linear hashing utilizes a linear powers-of-two algorithm whereas regular hashing employs the modulus of the hashing function's value.

• Key

Partitioning by key is similar to partitioning by hash, except that where hash partitioning employs a user-defined expression, the hashing function for key partitioning is supplied by the MySQL server.

PARTITION BY KEY(s1)

PARTITIONS 10;

 

Note: Several partitions of different types can't be created. If you change Partition type then all partitions and subpartitions created before will disappear.

 

Partition expression

Field, expression, exact value or list of values which defines data division logic for partitions.

 

 

Specify Partition name and Comment for the created partition.

Partition value is used only in partitions by range. It defines range of values. All records that meet the 'LESS THAN<PARTITION VALUE' are included in partition.

List of values is available when creating partition by list. Press the ellipsis button to open dialog allowing you to define the list of values.

 

 

Specify value and click Add to include this value in list.

Select a value in the list and click Replace to insert new value instead of the selected one.

You can change position of the selected value using the Up and Down buttons.

When done, click OK to apply changes.

 

Subpartition type

It is the further division of each partition in a partitioned table. If No partition is selected no actions for subpartitions are enabled in the context menu. Hash and Linear hash types for subpartitions follow the same syntax rules as for partitions. The Key subpartitioning differs in the way that for subpartitions you need to specify field manually though for partitions it is defined automatically (if not specified).

 

Note: In MySQL version 5.5 it is possible to create subpartitions for only range or list partitions.

 

Subpartition expression

Field, expression or exact value which defines data division logic for subpartitions.

 

Note: Subpartition type and expression defined in these fields will be applied to all subpartitions of the table.

 

The context menu allows you to manage partitions and subpartitions. Right-click an element to:

create new partition;

edit partition;

delete partition;

add subpartition;

edit subpartition's name;

delete subpartition (subpartition will be deleted from all partitions).

 

For your convenience all the actions are duplicated on the navigation bar.

 

Note: Some actions can be unavailable depending on the element type selected.