A Step-by-Step Guide to Create Partitions on MySQL table

1.What is partitioning?

Partitioning is a database design technique which is used to improves performance, manageability, simplifies maintenance and reduce the cost of storing large amounts of data. Partitioning can be achieved without splitting your MySQL tables by physically putting tables on individual disk drives.

Partitioning allows tables, indexes, and index-organized tables to be subdivided into smaller pieces, therefore queries that access only a fraction of the data can run faster because there are fewer data to scan.

2. Partitioning methods

2.1 Horizontal partitioning-

Horizontal partitioning divides a table into multiple tables. Each table then contains the same number of columns, but fewer rows.

For example, a table that contains 1 billion rows could be partitioned horizontally into 12 tables, with each smaller table representing one month of data for a specific year.

2.2 Vertical Partitioning-

Vertical partitioning involves creating tables with fewer columns and using additional tables to store the remaining columns. Normalization also involves this splitting of columns across tables, but vertical partitioning goes beyond that and partitions columns even when already normalized.

3. Benefits of partitioning

A popular and favorable application of partitioning is in a distributed database management system. The main purpose of partitioning is maintainability and performance. Your query performance will be much better as compared to the non-partitioned table.

4. Partitioning criteria

Range partitioning
List partitioning
Composite partitioning
Round-robin partitioning
Hash partitioning

Here we are discussing Hash partitioning and Range partitioning.

5. Creating partition on a table

Before creating range partition be sure that-

1. The column on which you are partitioning(range partitioning) the table, is a part of every unique key in that table otherwise, you will not be able to make partition of that table.

2. You are partitioning the table on the column(s) which is/are most commonly used in your queries otherwise, there will be no benefit of creating partitions.

Suppose that we have a MySQL table with the following schema

CREATE TABLE `testing_user` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) DEFAULT NULL,
 `email` varchar(100) DEFAULT NULL,
 `country` varchar(50) DEFAULT NULL,
 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

We will be creating some partitions on the MySQL table to understand how to create the partitions.

HASH Partitioning

Partitioning by HASH is used primarily to ensure an even distribution of data among a predetermined number of partitions. You have to specify how many partitions you want to create in a particular table.

In below query the table testing_user will be partitioned into 10 even size partitions

  ALTER table testing_user 
  PARTITION BY HASH(id) 
  PARTITIONS 10;

Suppose you want to partition the table on the basis of year in which the user was created

  ALTER table testing_user
  PARTITION BY HASH( YEAR(created) )
  PARTITIONS 10;

The above query will create 10 even size partition of the table testing_user on the basis of id and year in which the user was created.

Range partitioning

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. Ranges should be contiguous but not overlapping and are defined using the VALUES LESS THAN operator.

   ALTER TABLE testing_user  PARTITION BY RANGE (id)
  (
    PARTITION p1 VALUES LESS THAN (100000),
    PARTITION p2 VALUES LESS THAN (200000), 
    PARTITION p3 VALUES LESS THAN (300000),  
    PARTITION p4 VALUES LESS THAN (400000),
    PARTITION p5 VALUES LESS THAN (500000),
    PARTITION p6 VALUES LESS THAN (600000)
    PARTITION p7 VALUES LESS THAN MAXVALUE 
  );

How To Import and Export Databases (MySQL or MariaDB)

5. Restrictions and Limitations on Partitioning

Prohibited constructs: The following constructs are not permitted in partitioning expressions:
- Stored procedures, stored functions, UDFs, or plugins.
- Declared variables or user variables.
Table locks: The process executing a partitioning operation on a table takes a write lock on the table. Reads from such tables are relatively unaffected; pending INSERT and UPDATE operations are performed as soon as the partitioning operation has completed.
The maximum number of partitions: The maximum possible number of partitions for a given table (that does not use the NDB storage engine) is 1024. This number includes subpartitions.
FULLTEXT indexes: Partitioned tables do not support FULLTEXT indexes or searches. This includes partitioned tables employing the MyISAM storage engine.
Temporary tables: Temporary tables cannot be partitioned.