How to Partition a MySQL Table Using Navicat

Jul 23, 2021 by Robert Gravelle

In last week’s blog we learned about the potential uses and advantages to utilizing Database Partitioning when working with large data sets. In today’s follow-up, we’ll create a MySQL partition in Navicat for MySQL using the HASH partitioning criteria.

In Navicat, you’ll find the Partition button on the Options tab of the Table Designer, at the bottom of the page:

partition_button (65K)

Click this button to open the Partition dialog.

Creating a HASH Partition on a Table

The very first control on the Partition dialog is the Partition By drop-down:

partition_by_dropdown (17K)

The types of partitioning supported depend on the database type and version. Here are the options that you’ll find in Navicat for MySQL 7:

Range partitioning: Range (or Interval) partitioning is useful when organizing similar data – especially date and time data. Hence, Range partitioning is ideal for partitioning historical data.
List partitioning: Explicitly maps rows to partitions based on discrete values. For example, all the customers for southern states could be stored in one partition while customers from northern states would be stored in different partitions.
Composite partitioning: Partitions on multiple dimensions, based on identification by a partitioning key. For example, you may decide to store data for a specific product type in a read-only, compressed format, and keep other product type data uncompressed. Composite partitioning also increases the number of partitions significantly, which may be beneficial for efficient parallel execution.
Round-robin partitioning: Assigns rows in a round-robin manner to each partition so that each partition contains a more or less equal number of rows and load balancing is achieved. In this case there is no partition key, so rows are distributed randomly across all partitions.
Hash partitioning: Randomly distributes data across partitions based on a hashing algorithm, rather than grouping similar data. Useful for times when it is not obvious in which partition data should reside, although the partitioning key can be identified. Hence, data is distributed such that it does not correspond to a business or a logical view of the data, as it does in Range partitioning.

Some Caveats

In order to benefit from Partitioning, you’ll want to make sure that:

If you do supply the column on which to partition the table, that it is a part of every unique key in that table.
You are partitioning the table on the column(s) which is/are most commonly utilized in your queries. Otherwise, there will be no benefit from creating partitions.

Defining the Partition Details

The Partition dialog supports many options, including subpartitions as well as the ability to manually create partition definitions. However, for a simple HASH partition, we only need to provide the partition criteria, (table column) and number of partitions:

partition_dialog (60K)

Click the OK button to create the partition in one easy step!

On the SQL Preview tab, you can view the SQL statement that was generated by Navicat:

ALTER TABLE `sakila2`.`film` PARTITION BY HASH (actor)
PARTITIONS 10
(PARTITION `p0` MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `p1` MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `p2` MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `p3` MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `p4` MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `p5` MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `p6` MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `p7` MAX_ROWS = 0 MIN_ROWS = 0 ,

PARTITION `p8` MAX_ROWS = 0 MIN_ROWS = 0 ,
PARTITION `p9` MAX_ROWS = 0 MIN_ROWS = 0 )
;

In today’s blog, we created a MySQL partition in Navicat for MySQL using HASH partitioning criteria.

Interested in Navicat for MySQL? You can try it for free for 14 days!

Rob Gravelle resides in Ottawa, Canada, and has been an IT Guru for over 20 years. In that time, Rob has built systems for intelligence-related organizations such as Canada Border Services and various commercial businesses. In his spare time, Rob has become an accomplished music artist with several CDs and digital releases to his credit.