Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

First, some English. Estimation is not the same as measuring. If I wanted to measure, I would do so ;) (I might actually do that later). Also I have a dev question posed related to this post.

I would like to store data in a MySQL table, The data type of the table is date, long, long, long, long, short int , where i would have a composite key based on date, long, long.

There could be more indexes as well. (Edit) It seems that it would be 42 bytes of data plus the indexes. You can use 1 million records inserted every day for data growth. Should I just say 42 million bits would be added every day (I guess there is more, based on configuration)

So looking at this http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html it comes out to be

I can have lets say 1 million rows added every day. How much would I rack up in Diskspace? Any references would be greatly appreciated (I will try to find some and post it here as well)

share|improve this question

2 Answers 2

up vote 1 down vote accepted

You should ask MySQL's information_schema for that information.

Here is how:

SELECT AVG_ROW_LENGTH FROM information_schema.tables
WHERE table_schema='whateverdatabase'
AND table_name='whatevertable';

Now you can use multiples of this value for your estimates. So, in you case, that would be:

SELECT (AVG_ROW_LENGTH * 1000000) AverageSpaceForOneMillionRows
FROM information_schema.tables
WHERE table_schema='whateverdatabase'
AND table_name='whatevertable';
share|improve this answer
    
i guess this is better than an estimate, but it means that schama has to be created and should be tested against mysql (one cannot give estimate from the head) –  geoaxis Aug 25 '11 at 7:57

Wait, I calculate this like that according to this:

4 times long = 32 Byte
1 time shortint = 2 Byte
1 time datetime = 8 Byte

which sums up to 42 Byte = 336 Bit. Plus the overhead of each row (not known). And plus the index of at least 32 Byte. So one row is round about 80 Byte or 640 Bit.

1 Million times 80 Byte equals 80,000,000 Bytes which is 80 Megabyte (not Mebibyte). Which is only a rough estimation.

share|improve this answer
    
yeah thanks for your answer, i surely was confusing bytes abd bits there, –  geoaxis Aug 24 '11 at 21:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.