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;

MySQL Database Definition
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 Table Definition
MySQL create table statement

Step 3: Verifying table definition:

SHOW CREATE table parttable;

MySQL Table definition
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
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
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
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.

12 comments:

  1. Any how I am here now and would just like to say thanks a lot for a tremendous post and an all-round exciting blog
    nebosh course in chennai
    safety course in chennai

    ReplyDelete
  2. I don’t have time to go through it all at the minute but I have saved it and also added in your RSS feeds, so when I have time I will be back to read more, Please do keep up the awesome job.
    nebosh course in chennai
    offshore safety course in chennai

    ReplyDelete
  3. Well with your permission let me grab your RSS feed to keep updated with forthcoming post. Thanks a million, and please keep up the gratifying work.
    fire and safety course in chennai
    safety course in chennai

    ReplyDelete
  4. Excellent Post as always and you have a great post and i like it


    โปรโมชั่นGclub ของทางทีมงานตอนนี้แจกฟรีโบนัส 50%
    เพียงแค่คุณสมัคร Gclub กับทางทีมงานของเราเพียงเท่านั้น
    ร่วมมาเป็นส่วนหนึ่งกับเว็บไซต์คาสิโนออนไลน์ของเราได้เลยค่ะ
    สมัครสมาชิกที่นี่ >>> Gclub online

    ReplyDelete
  5. This is really an amazing blog. Your blog is really good and your article has always good thank you for information.

    เว็บไซต์คาสิโนออนไลน์ที่ได้คุณภาพอับดับ 1 ของประเทศ
    เป็นเว็บไซต์การพนันออนไลน์ที่มีคนมา สมัคร Gclub Royal1688
    และยังมีเกมส์สล็อตออนไลน์ 1688 slot อีกมากมายให้คุณได้ลอง
    สมัครสมาชิกที่นี่ >>> Gclub Royal1688

    ReplyDelete