Do I have to drop the primary key or reload the table data for partitioning in MySQL InnoDB?

157 views Asked by At

I have to partition a few tables by date range. Do I have to drop the primary or reload the complete data to be able to partition these tables?

Some of the tables do contain a lot more than 50 million rows.

alter table temp_table_test1
partition by range (unix_timestamp(created_at))
(
    partition p01 values less than (unix_timestamp('2015-02-01')),
    partition p02 values less than (unix_timestamp('2015-02-01')),
    partition p03 values less than (unix_timestamp('2015-02-01')),
    partition p04 values less than (unix_timestamp('2015-02-01')),
    partition p02 values less than (maxvalue)
);

This is the script I am using.

1

There are 1 answers

1
smnbbrv On BEST ANSWER

You don't need to do anything if you have no partitioning yet. According to this thread this will be done automatically:

you can use ALTER TABLE to add partitioning to the table, keep in mind though that this will actually create the new partitioned table first, then copy over all the existing data, and finally drop the old unpartitioned table. So this operation may take a while and will temporarily use twice the disk space (which in the case of InnoDB is not given back to the operating system ...)