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.

10 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. we provide the world's best web hosting like shared, VPS, Dedicated and Wordpress web hosting. You will get the discount and Coupon code on bluehost hosting. Bluehost VPS coupon code
    Bluehost discounts coupons

    ReplyDelete
  4. I appreciate your hard working. Thanks for the useful info. Here is Hostinger Coupons for you

    ReplyDelete
  5. Great work keep going on. Like to see more post. Here I was looking for Dr Strains CBD Coupons you can also take look on the offer if you like.

    ReplyDelete