Partitioning a table in Mysql5.1.x with Date, Time, Timestamp

This blog describes how to use table partition in Mysql-5.1.x to enhance query execution in Mysql.

Mysql partition for a table having "Timestamp" datatype of a column never works properly.
It will work with DateTime datatype. So you have to change datatype from "Timestamp" to "DATETIME".

So change your "timestamp" datatype to "DateTime" and then following these lines:-
Both datatypes are almost same, however DateTime takes 8 bytes in memory while Timestamp takes 4 bytes.

//Description of table.
mysql> desc Table_Name;
+------------------+-------------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------+-------------+------+-----+---------------------+-------+
| Id | int(11) | NO | PRI | NULL | |
| TxnId | int(11) | NO | PRI | NULL | |
| Time | datetime | NO | PRI | 0000-00-00 00:00:00 | |
| Name | varchar(64) | NO | | NULL | |
----------------------------------------------
----------------------------------------------
----------------------------------------------
| BMiningClusterId | int(11) | YES | | NULL | |
+------------------+-------------+------+-----+---------------------+-------+


//Changing datatype from timestamp to DATETIME
1) ALTER TABLE Table_NAME modify Time datetime not null default '0000-00-00 00:00:00';

//Checking partition, change the database_name and Table_name
2) select PARTITION_NAME,PARTITION_EXPRESSION,TABLE_ROWS, TABLE_NAME from information_schema.PARTITIONS where TABLE_SCHEMA='database_name' and TABLE_NAME='TableName';

//Remove existing partitions, data will not be deleted.
3) ALTER TABLE Table_Name REMOVE PARTITIONING;

//Create new partitions.
4) alter table Table_NAME partition by range(to_days(Time)) (
PARTITION p2009_1 values less than (to_days('2009-02-01 00:00:00')),
PARTITION p2009_2 values less than (to_days('2009-03-01 00:00:00')),
PARTITION p2009_3 values less than (to_days('2009-04-01 00:00:00')),
PARTITION p2009_4 values less than (to_days('2009-05-01 00:00:00')),
PARTITION p2009_5 values less than (to_days('2009-06-01 00:00:00')),
PARTITION p2009_6 values less than (to_days('2009-07-01 00:00:00')),
PARTITION p2009_7 values less than (to_days('2009-08-01 00:00:00')),
PARTITION p2009_8 values less than (to_days('2009-09-01 00:00:00')),
PARTITION p2009_9 values less than (to_days('2009-10-01 00:00:00')),
PARTITION p2009_10 values less than (to_days('2009-11-01 00:00:00')),
PARTITION p2009_11 values less than (to_days('2009-12-01 00:00:00')),
PARTITION p2009_12 values less than (to_days('2010-01-01 00:00:00')),


PARTITION p2010_1 values less than (to_days('2010-02-01 00:00:00')),
PARTITION p2010_2 values less than (to_days('2010-03-01 00:00:00')),
PARTITION p2010_3 values less than (to_days('2010-04-01 00:00:00')),
PARTITION p2010_4 values less than (to_days('2010-05-01 00:00:00')),
PARTITION p2010_5 values less than (to_days('2010-06-01 00:00:00')),
PARTITION p2010_6 values less than (to_days('2010-07-01 00:00:00')),
PARTITION p2010_7 values less than (to_days('2010-08-01 00:00:00')),
PARTITION p2010_8 values less than (to_days('2010-09-01 00:00:00')),
PARTITION p2010_9 values less than (to_days('2010-10-01 00:00:00')),
PARTITION p2010_10 values less than (to_days('2010-11-01 00:00:00')),
PARTITION p2010_11 values less than (to_days('2010-12-01 00:00:00')),
PARTITION p2010_12 values less than (to_days('2011-01-01 00:00:00'))
);


//Addition of new partitions
4) alter table Table_NAME add partition(
partition p2011_1 values less than (to_days('2011-02-01 00:00:00')),
partition p2011_2 values less than (to_days('2011-03-01 00:00:00')),
partition p2011_3 values less than (to_days('2011-04-01 00:00:00')),
partition p2011_4 values less than (to_days('2011-05-01 00:00:00')),
partition p2011_5 values less than (to_days('2011-06-01 00:00:00')),
partition p2011_6 values less than (to_days('2011-07-01 00:00:00')),
partition p2011_7 values less than (to_days('2011-08-01 00:00:00')),
partition p2011_8 values less than (to_days('2011-09-01 00:00:00')),
partition p2011_9 values less than (to_days('2011-10-01 00:00:00')),
partition p2011_10 values less than (to_days('2011-11-01 00:00:00')),
partition p2011_11 values less than (to_days('2011-12-01 00:00:00')),
partition p2011_12 values less than (to_days('2012-01-01 00:00:00'))
);

//Again watching partitions that gets created.
5) select PARTITION_NAME,PARTITION_EXPRESSION,TABLE_ROWS, TABLE_NAME from information_schema.PARTITIONS where TABLE_SCHEMA='database_name' and TABLE_NAME='TableName';

//Simple select query.
6) select * from TABLE_NAME where Time>'2009-09-01 00:00:00' and Time<='2009-09-30 23:59:59'; //Checking query execution and advantage of partition, this will show you which partitions get scanned while executing this query. 7) explain partitions select * from TABLE_NAME where Time>'2009-09-01 00:00:00' and Time<='2009-09-30 23:59:59';

-----------------------------------End---------------------------------------