Getting an approximation of a MySql database entry

Question

I hava a database with the following structure.

How can I determine, what storage space will a number of such entries take, if:

I'll run this on a MySql server (innoDB). The int columns will have small values (1-30 at most), except one, which will have a value between [1-400].

There will be 40 entries produced every day.

i don't understand you question, but `id int(11)` allow eleven digits means you can enter in int upto '99999999999'. ect. similarly for your size. — Snopzer, Jun 15 '16 at 05:51
Use `TINYINT` or `SMALLINT`. Also, https://dev.mysql.com/doc/refman/5.7/en/storage-requirements.html — hjpotter92, Jun 15 '16 at 05:56
See Rolando's [answer](http://dba.stackexchange.com/a/46098) and Rick's comment and his own [answer](http://dba.stackexchange.com/a/114522) related to character sets. — Drew, Jun 15 '16 at 14:05

score 1 · Answer 1 · answered Jun 15 '16 at 06:25

1

Mysql manual has a section on data type storage space. Since you are using numbers and dates, which are stored on fixed length, it is pretty easy to estimate the storage space: each integer column requires 4 bytes (even if you store the numeric value of 1 in it), the date column requires 3 bytes.

You may reduce the storage requirements by using smaller integer types.

answered Jun 15 '16 at 06:25

Shadow

33,525
10
51
64

Also you should add that NULL takes no space with InnoDB, according to this: http://stackoverflow.com/questions/229179/null-in-mysql-performance-storage – rlanvin Jun 15 '16 at 06:26
@rlanvin unless you expect to have a bunch of nulls in every record, it does not matter when you **estimate** the required data storage space. If you expect to have a bunch of nulls, then I would consider using a different table structure anyway. – Shadow Jun 15 '16 at 06:39
well you never know, there are perfectly valid use cases for nulls. Anyway, I just thought it was worth mentioning for the sake of completeness. – rlanvin Jun 15 '16 at 07:10
I never said there was no valid uses cases for nulls. My point is that an occasional null value here and there will not significantly impact the space needed for a table, hence there is no point in considering this saving in an estimate - unless you know that you will have not just a few, but lots of nulls. – Shadow Jun 15 '16 at 08:59

score 0 · Answer 2 · answered Jun 28 '16 at 22:16

What you have described comes in the "tiny" category. After adding the 40 entries for the first day, you will be a few hundred bytes, occupying one 16KB InnoDB block.

After a year, you might have a megabyte -- still "tiny" as database tables go.

Switch to SMALLINT, add UNSIGNED if appropriate; that will cut it down noticeably. Follow the various links in the comments and other answers; they give you more insight.

Getting an approximation of a MySql database entry

2 Answers2