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.
