Table structure in VERY large DB
Hi!
I have a database that needs to store the data of 1 million of attributes (data_Id), every 15 minutes, every day (96 million values by day), and 90% of the values are zero.
So I use two tables for the data, a TINYINT to indicate the interval of time of that day and version MySQL v5.1 with partition by day:
CREATE TABLE ` data_int ` (
`date` DATE NOT NULL,
`rop` TINYINT UNSIGNED NOT NULL,
`data_Id` MEDIUMINT UNSIGNED NOT NULL,
`value` INT NOT NULL,
INDEX `data_Id` (`data_Id`)
)
ENGINE = MYISAM;
CREATE TABLE `data_zero ` (
`date` DATE NOT NULL,
`rop` TINYINT UNSIGNED NOT NULL,
`data_Id` MEDIUMINT UNSIGNED NOT NULL,
INDEX `data_Id` (`data_Id`)
)
ENGINE = MYISAM;
But the index is getting too big, almost the same size of the table (600MB*2 by day for the table data_zero), and the inserts are too slow, even with a key buffer of 512MB.
So I tried another approach, with a column by rop, but then I have to put all the values as INT, and the table gets really big, because the zeros will have 4 bytes
Anyone have better ideas?
Thx in advance