Reorganize last partition table in mysql EXPLAINED!!!

When you new data start to use last partition in table that you partitioned it is time that from that last partition create new partitions.

It is neccesery to this because when you create partitions your last partition has parameter MAXVALUE.
Check this post for introduction http://sysadmin-tricks.blogspot.com/2014/03/partitioning-of-mysql-database-table.html .

This means that database will continue to write data in that last partition when all values that are younger that that last defined date. In are case that means that all date larger that 2013-08-21 00:00:00 is stored in partition with MAXVALUE parameter.

When you start to use partition p2014, this means that previuos partition are done with entering data. So for instance, all your partition stores data for one day period but this last partition can store data for much larger period of time because it has MAXVALUE  parameter defined during creation of it.

When this happens it is time to reorganize that last partition. Why is necceseru to do this? Because after some time, this partition becames so large and at that time point of creating partitions on that table loose sence. If your partitions store data for one day and they are 1GB big, this last would be much, much bigger because in it you would have (this is example) data for more that one day.

OK, so here we go!

#mysql
mysql>use database1
mysql>alter table SystemEvents reorganize partition p2014 into
 ( partition p20130907 values less than (to_days('2013-09-07')),
   partition p20130908 values less than (to_days('2013-09-08')),
   partition p20130909 values less than (to_days('2013-09-09')),
   partition p20130910 values less than (to_days('2013-09-10')),
   partition p20130911 values less than (to_days('2013-09-11')),
   partition p20130912 values less than (to_days('2013-09-12')),
   partition p20130913 values less than (to_days('2013-09-13')),
   partition p20130914 values less than (to_days('2013-09-14')),
   partition p20130915 values less than (to_days('2013-09-15')),
   partition p20130916 values less than (to_days('2013-09-16')),
   partition p20130917 values less than (to_days('2013-09-17')),
   partition p20130918 values less than (to_days('2013-09-18')),
   partition p20130919 values less than (to_days('2013-09-19')),
   partition p20130920 values less than (to_days('2013-09-20')),
   partition p20130921 values less than (to_days('2013-09-21')),
   partition p20130922 values less than (to_days('2013-09-22')),
   partition p20130923 values less than (to_days('2013-09-23')),
   partition p20130924 values less than (to_days('2013-09-24')),
   partition p20130925 values less than (to_days('2013-09-25')),
   partition p20130926 values less than (to_days('2013-09-26')),
   partition p20130927 values less than (to_days('2013-09-27')),
   partition p20130928 values less than (to_days('2013-09-28')),
   partition p20130929 values less than (to_days('2013-09-29')),
   partition p20130930 values less than (to_days('2013-09-30')),
   partition p2014 values less than (MAXVALUE));

This will create new partition that will store data for one day from that big partition p2014.
As you can see last partition has again MAXVALUE parameter.