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.

You need to split each partition into two partitions? Something like this (please verify the syntax):

ALTER TABLE table1  REORGANIZE PARTITION p2015half1 INTO
    partition `p0` values less than ('2015-10-01'),
    partition `p0` values less than ('2016-01-01') ;

That is, take any number of partitions (1 in this case), copy the data into any number of new partitions (2 in this case).

But I need to ask "why"? Would you show us SHOW CREATE TABLE and explain why you chose to use PARTITIONing. I ask this question because everyone seems to have the impression that partitioning is beneficial. Often it provides no benefit, especially not in performance.

The next question has to do with "billions of rows". To do anything with that much data takes a long time -- simply because of I/O speed. What kinds of queries are you doing on the table? Do you have summary tables? Do you purge "old" data?

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.