MySQL Partitioning Example

This post is about partitioning a MySQL table by year by using range partition type. This post does not deal with the theories on partition, if you would like to understand the complete partitioning methods and types visit the official MySQL documentation. In this post we are directly focus on the implementation steps, assuming that we have knew the basics of MySQL partitioning types and its usages.

Step 1: Create a test database:

CREATE DATABASE partitiontest;USE partitiontest;

Database definition

Step 2: Create a test table with partition definition:

CREATE TABLE parttable (rollno INT, name VARCHAR(50), birthdate DATE)PARTITION BY RANGE (YEAR(birthdate))(PARTITION p0 VALUES LESS THAN (1990),PARTITION p1 VALUES LESS THAN (1995),PARTITION p2 VALUES LESS THAN (2000),PARTITION p3 VALUES LESS THAN (2005));

MySQL create table statement

Step 3: Verifying table definition:

SHOW CREATE table parttable;

MySQL Table Definition

Step 4: Populate some data in the partitioned table.

INSERT INTO parttable  VALUES
(1, 'Suresh', '2003-10-15'),
(2, 'Ramesh', '1993-11-05'),
(3, 'John', '1996-03-10'),
(4, 'Steve', '1982-01-10'),
(5, 'Arun', '2004-05-09'),
(6, 'Michael', '1987-06-05'),
(7, 'ram', '2001-11-22'),
(8, 'eka', '1992-08-04'),
(9, 'melinda', '1984-09-16'),
(10, 'lavanya', '1998-12-25');

Populating values in MySQL table

Step 5: Selecting data from partitioned table:

SELECT * FROM parttable WHERE birthdate BETWEEN '1995-01-01' AND '1999-12-31';

Selecting data from MySQL table

Step 6: Selecting data from individual partition:

SELECT * FROM parttable PARTITION (p0);SELECT * FROM parttable PARTITION (p1);SELECT * FROM parttable PARTITION (p2);SELECT * FROM parttable PARTITION (p3);

Selecting data from MySQL table partition

I hope this post will give you basic understanding of implementation of partitioning in MySQL, in the future posts let us discuss more about partitioning.