Home / server / Questions / 304585
Accepted
geoaxis
Asked: 2011-08-25 08:36:07 +0800 CST

Estimating data space which will be used my Mysql upon adding a table

  • 772

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)

mysql

2 Answers

  1. 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.

    • 1
  2. Best Answer

    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';
    • 1