How to estimate MYSQL database size for known column range

Question

I am trying to estimate database size for know column range. I have searched on it but cant find any proper rules.

I have created a database with single table and this table have 40 Columns. One column is a int primary key and remaining 39 columns are LONGTEXT type.

I am expecting 2 million records.

CREATE TABLE IF NOT EXISTS `TempTable` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
    `Column1` longtext,
    `Column2` longtext,
    --
    --
    --

    `Column39` longtext,

  PRIMARY KEY (`Id`)
)

How can I do this? Do I need to insert fake data to calculate the size?

@RolandStarke ~4 GB for 2 million rows? – Shoaib Ijaz Apr 22 '16 at 12:09 — Shoaib Ijaz, Apr 22 '16 at 12:09

score 2 · Accepted Answer · edited May 23 '17 at 12:32

Using the Data Type Storage Requirements you can estimate the table size:

The int is 4 bytes, note that (11) only indicates the maximum number of digits to show (see this answer).
(L+4) per string column, L being the length in bytes of the string. But note that when you use Unicode (utf8) not all characters use the same number of bytes.

So the size would be:

R * ( 4 + 39*(L+4) )

With R being the number of records, and L the average string size.

Taking R=2*10⁶ and L=1000 bytes, this gives about:

2*10⁶ * ( 4 + 39 ( 1004 ) ) = 7.832 * 10¹⁰ bytes

which is about 72.9 GiB, for other L's:

L (bytes)       : 10    100   1000   10000
Table size (GiB):  1    7.5   72.9   726.7

Some further notes:

The actual size depends on the storage engine.
Some engines also support data compression, such as InnoDB.
If you use InnoDB it uses 'pages', so the size are multiples of the page size, see this answer.
See this related question: How to estimate/predict data size and index size of a table in MySQL

How to estimate MYSQL database size for known column range

1 Answers1