Each row on these tables are expected to be unique for both `id`+`time_range_beg` fields, so I setup the primary key on this, but this could be `id`+`time_range_end` as well, knowing that the defined time ranges never overlap. Some of these tables are very big (currently 150,000,000 or even 500,000,000 rows), and they are constantly growing by the end. As they are not very efficient to query, I am trying several kinds of partitioning in order to find the most effective one. The problem is that, according to partition scheme, if I change the “PARTITION BY RANGE” field, I can get the following message: “A PRIMARY KEY must include all columns in the table's partitioning function”. I order to fix this, I need to change the primary key, for setting one including the field I want to use. But if I do, for example:
ALTER TABLE `my_timeranges_table` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`,`time_range_end`);
I get the same error message because the partitioning is already
set on the primary key that I want to drop before adding the new
one.
So what I actually have to do is the following:
ALTER TABLE `my_timeranges_table` REMOVE PARTITIONING;
ALTER TABLE `my_timeranges_table` DROP PRIMARY KEY, ADD PRIMARY KEY (`id`,`time_range_end`);
ALTER TABLE `my_timeranges_table` PARTITION BY RANGE (`time_range_end`) (<new partitioning scheme>);
But if I do this, as my tables are very big, this implies copying all of their contents on each statement, which may need about several hours for each (on a not very performant computer, I admit).
In order to do it more quickly, I tried:
ALTER TABLE `my_timeranges_table`
REMOVE PARTITIONING,
DROP PRIMARY KEY,
ADD PRIMARY KEY (`id`,`time_range_end`),
PARTITION BY RANGE (`time_range_end`)
(<new partitioning scheme>);
But unfortunately, this doesn't work:
MariaDB doesn't seem to accept changing partitions and index keys in the same statement (despite the fact that the ALTER statement definition syntax would theoretically allow it).
Thus I get another error message.
So I have some questions: