Table structure in VERY large DB
Posted by: Daniel Silva
Date: September 27, 2007 10:28AM

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

Options: ReplyQuote


Subject
Written By
Posted
Table structure in VERY large DB
September 27, 2007 10:28AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.