Here's the question...
Considering 192 trillion records, what should my considerations be?
My main concern is speed.
Here's the table...
CREATE TABLE `ref` (
`id` INTEGER(13) AUTO_INCREMENT DEFAULT NOT NULL,
`rel_id` INTEGER(13) NOT NULL,
`p1` INTEGER(13) NOT NULL,
`p2` INTEGER(13) DEFAULT NULL,
`p3` INTEGER(13) DEFAULT NULL,
`s` INTEGER(13) NOT NULL,
`p4` INTEGER(13) DEFAULT NULL,
`p5` INTEGER(13) DEFAULT NULL,
`p6` INTEGER(13) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY (`s`),
KEY (`rel_id`),
KEY (`p3`),
KEY (`p4`)
);
Here's the queries...
SELECT id, s FROM ref WHERE red_id="$rel_id" AND p3="$p3" AND p4="$p4"
SELECT rel_id, p1, p2, p3, p4, p5, p6 FROM ref WHERE id="$id"
INSERT INTO rel (rel_id, p1, p2, p3, s, p4, p5, p6)
VALUES ("$rel_id", "$p1", "$p2", "$p3", "$s", "$p4", "$p5", "$p6")
Here's some notes...
The SELECT's will be done much more frequently than the INSERT. However, occasionally I want to add a few hundred records at a time.
Load-wise, there will be nothing for hours then maybe a few thousand queries all at once.
Don't think I can normalize any more (need the p values in a combination)
The database as a whole is very relational.
This will be the largest table by far (next largest is about 900k)
UPDATE (08/11/2010)
Interestingly, I've been given a second option...
Instead of 192 trillion I could store 2.6*10^16 (15 zeros, meaning 26 Quadrillion)...
But in this second option I would only need to store one bigint(18) as the index in a table. That's it - just the one column. So I would just be checking for the existence of a value. Occasionally adding records, never deleting them.
So that makes me think there must be a better solution then mysql for simply storing numbers...
Given this second option, should I take it or stick with the first...
[edit] Just got news of some testing that's been done - 100 million rows with this setup returns the query in 0.0004 seconds [/edit]
