MySQL Storage Requirements for Numeric Types in InnoDB?

Geoff Millikan
Geoff Millikan used Ask the Experts™
on
In MySQL5.0 I calculated the storage requirements for the below data types in InnoDB.  Can you confirm this is correct?

http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

MEDIUMINT()   = 3 bytes
DECIMAL(3)    = 4 bytes
DECIMAL(9)    = 4 bytes
DECIMAL(10)   = 5 bytes
DECIMAL(16)   = 8 bytes
DECIMAL(2,2)  = 8 bytes
DECIMAL(12,2) = 9 bytes

Open in new window


Did I get that right for InnoDB?

Thanks,

http://www.t1shopper.com/
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
According to http://www.learn-mysql-tutorial.com/TableTypes.cfm. The storage requirements for InnoDB tables are much greater than those for equivalent MyISAM tables (up to twice as big).

Link you provided only valid for MyISAM.

Author

Commented:
So, are my storage calculations correct?

Author

Commented:
> MEDIUMINT()   = 3 bytes

OK

> DECIMAL(3)    = 4 bytes

3 leftover digits = 2 bytes

> DECIMAL(9)    = 4 bytes

OK

> DECIMAL(10)   = 5 bytes

OK

> DECIMAL(16)   = 8 bytes

OK

> DECIMAL(2,2)  = 8 bytes

This is 2 leftover digits for each part, 1 byte for each part, so two bytes total.

> DECIMAL(12,2) = 9 bytes

12 is 4 bytes plus 2 bytes for 3 leftover digits. 2 is 1 byte for the two leftover digits. 4 + 3 + 2 = 9 bytes, so OK.
Better to post the answer from MySQL tech support, then accept it as solution.
http://dev.mysql.com/doc/refman/5.1/en/precision-math-decimal-changes.html

Sorry, some parts of my answer were wrong. Here are the corrections:

> > DECIMAL(2,2)  = 8 bytes
> This is 2 leftover digits for each part, 1 byte for each part, so two bytes total.

This is really as decimal number with no digits before the decimal point and two digits after, so numbers from -0.99 to 0.99. With no part before the decimal point that has no space and the two leftover digits to the right take 1 byte.

> > DECIMAL(12,2) = 9 bytes
> 12 is 4 bytes plus 2 bytes for 3 leftover digits. 2 is 1 byte for the two leftover digits. 4 + 3 + 2 = 9 bytes, so OK.

This is ten digits before the decimal point, so one allocation of four bytes for nine digits plus one byte for one leftover digit. And two digits after the decimal point, so two leftover digits at one byte. A total of six bytes

Author

Commented:
> Now let's say I have a data type DECIMAL(12,2) with the below 4 rows in it.  Do each of the values take up the same 9 bytes of storage space?

Noting my correction to my earlier reply that it's really six bytes, not nine, then:

> A. 1.23
> B. 1234567890.12
> C. 0

Yes for those three, at six bytes each. InnoDB stored decimals internally as fixed length binary columns, not variable length.

> D. NULL

InnoDB's compact format stores NULL columns as one bit in a bit vector that has one bit allocated for each column that can have a NULL value. This bit vector is always allocated.

> So the above table with 4 rows would *theoretically* take up *about* 36 bytes?

18 bytes plus one byte for every eight or part of eight nullable columns.

> For example, a DECIMAL(18,9) column has nine digits on either side of the decimal point, so the integer part and the fractional part each require four bytes. A DECIMAL(20,6) column has fourteen integer digits and six fractional digits. The integer digits require four bytes for nine of the digits and three bytes for the remaining five digits. The six fractional digits require three bytes.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial