I have a table like:
create table table_a
(
id_a mediumint unsigned not null,
id_b tinyint unsigned not null,
id_c char(10) not null,
value tinyint unsigned not null,
TYPE enum ('a', 'b', 'c') not null,
DATE date not null,
constraint `unique`
unique (id_b, id_c, id_a, TYPE, DATE),
constraint fk_id_b_id_c
foreign key (id_b, id_c) references table_b (id_b, id_c)
on delete cascade,
constraint id_a
foreign key (id_a) references table_c (ID)
on delete cascade
);
create index table_a_value_index
on table_a (value);
create index table_a_new
on table_a (id_b, id_c, DATE, id_a);
create index mks_date
on table_a (DATE);
Now I would like to create partitions, but I am not sure how they will impact the RAM usage or the performance. I have tried the partitions like:
ALTER TABLE table_a
PARTITION BY RANGE (to_days(`date`)) (
PARTITION 2021_H1 VALUES LESS THAN (to_days('2021-07-01')),
PARTITION 2021_H2 VALUES LESS THAN (to_days('2022-01-01')),
PARTITION 2022_H1 VALUES LESS THAN (to_days('2022-07-01')),
PARTITION current VALUES LESS THAN (MAXVALUE)
) ;
But that hadn't the expected RAM, performance improvement.
My research has shown that smaller partitions could be better, but you should not have more than 50 partitions. If I would partition after each month, I could store the last 4 years (when max. 50 partitions are recommended), which would be enough.
But how much would that impact my RAM usage and/or the performance?
As far as I understood, the partitions are treated as separated tables, does that mean, that each partition will have their own indexes? The table has a size of 20GB+, but the indexes are 40GB+. It would be beneficial to reduce the loaded index size.
The most used indexes are unique and table_a_new. The filter for date is a specific date or a range of 6 months.
It is fine, that I will lose my foreign keys.
