MySQL Storage Requirements for Numeric Types in InnoDB?
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/r
Did I get that right for InnoDB?
Thanks,
http://www.t1shopper.com/
http://dev.mysql.com/doc/r
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 bytesDid I get that right for InnoDB?
Thanks,
http://www.t1shopper.com/
ASKER
So, are my storage calculations correct?
ASKER
> 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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
> 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.
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.
Link you provided only valid for MyISAM.