I have a mysql table whose partitions look as below
p2015h1 - Contains data where date < 2015-07-01 (Has data from 2016-06-01. Hence only month worth of data) p2015h2 - Contains data where date < 2016-01-01 p2016h1 - Contains data where date < 2016-07-01 p2016h2 - Contains data where date < 2017-01-01
I'd like the new partitions to be quarterly based as below -
p0 - Contains data where date < 2015-10-01 p1 - Contains data where date < 2016-01-01 p2 - Contains data where date < 2016-04-01 p3 - Contains data where date < 2016-07-01
I started by reorganizing the first partition & executed the below command. All went well.
alter table `table1` reorganize partition `p2015half1` into (partition `p0` values less than ('2015-10-01'));
Now as the existing partition
p2015h2 has data that includes data upto
2015-10-01, how could I move this part into the partition
p0 ? I would need to do the same thing with the other partitions too as I continue building the new ones.
I did try to remove partitioning on the table fully, but, the table is billions of rows in size & hence the operation will take days. Post this I will have to rebuild the partitions which will take days again. Hence, I decided to take the approach of splitting partitions.
I'm stuck at this point in time. I'd fully appreciate any guidance here please.