CODEWITHSUNDEEP
mysqlsqlpartitioningdatabase-partitioning
bnsmith
bnsmith

Reputation: 1697

Partitioning a MySQL table based on a column in another table

I'm working on designing a new database that will need to handle an enormous amount of data. It will be a data warehouse system, and will thus be organized around a central hub table:

create table hub(id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
date_time DATETIME NOT NULL, bit_of_data INT NOT NULL);

When this table grows very large, it seems that it will be necessary to partition it based on the 'date_time' column, with each partition being, say, one month of data. However, there will also be another table:

create table other_data(id BIGINT NOT NULL PRIMARY KEY, 
more_data INT NOT NULL, FOREIGN KEY(id) REFERENCES hub(id));

This second table will contain records for about 90% of the ids that appear in the main 'hub' table. I'd like to partition the 'other_data' table as well as the 'hub' table, and have the partitions basically match up with each other. Is there any way to partition the 'hub' table on a date range, and then also partition the 'other_data' table on the same date range?

Thanks!

Upvotes: 1

Views: 1227

Answers (1)

niktrs
niktrs

Reputation: 10066

This can be done only by adding a (redudant) date column in the other_data table.

Upvotes: 1

Related Questions