Mysql range partitioning on date column

Here is a simple example for partitioning a mysql table based on datetime column :

  • Creating a test table
mysql> create table test_partitions(id int,name varchar(50),tx_date datetime);
Query OK, 0 rows affected (11.83 sec)
  • Creating a initial partition :
alter table test_partitions PARTITION BY RANGE (to_seconds(tx_date)) (
partition test_partitions_16052016 values less than ( to_seconds('2016-05-17 00:00:00') ));
  • Adding new partition:
mysql> alter table test_partitions add partition ( partition test_partitions_17052016 values less than ( to_seconds('2016-05-18 00:00:00') ));
Query OK, 0 rows affected (10.87 sec)
Records: 0 Duplicates: 0 Warnings: 0
  • Verifying data in specific partition

Verify data in partition test_partitions_16052016 :

mysql> select * from test_partitions partition(test_partitions_16052016);
+------+---------+---------------------+
| id | name | tx_date |
+------+---------+---------------------+
| 1 | goutham | 2016-05-16 14:00:00 |
+------+---------+---------------------+
1 row in set (0.00 sec)

mysql> select * from test_partitions;
+------+---------+---------------------+
| id | name | tx_date |
+------+---------+---------------------+
| 1 | goutham | 2016-05-16 14:00:00 |
| 2 | testing | 2016-05-17 12:00:00 |
+------+---------+---------------------+
2 rows in set (0.00 sec)
  • Verifying row movement when partitioned column value is updated

Update a row present in some partition and see that updated row moves to correct partition :

mysql> UPDATE test_partitions set tx_date='2016-05-16 14:20:00' where id=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from test_partitions partition(test_partitions_16052016);
+------+---------+---------------------+
| id | name | tx_date |
+------+---------+---------------------+
| 1 | goutham | 2016-05-16 14:00:00 |
| 2 | testing | 2016-05-16 14:20:00 |
+------+---------+---------------------+
2 rows in set (0.00 sec)

Leave a Reply

Your email address will not be published. Required fields are marked *