CODEWITHSUNDEEP
mysql
Don Wool
Don Wool

Reputation: 267

Mysql DECIMAL storage

Hi I am creating a very big table using DECIMAL data types. its gonna be 50 million rows to start and grow from there, so I am concerned with storage. I need DECIMAL as I need exact representation, and the documentation is clear that if you want exact representation you must use DECIMAL.

The mysql manual is quite clear on DECIMAL storage reqs, stating :

As of MySQL 5.0.3, values for DECIMAL columns are represented using a binary format that packs nine decimal (base 10) digits into four bytes. Storage for the integer and fractional parts of each value are determined separately. Each multiple of nine digits requires four bytes, and the “leftover” digits require some fraction of four bytes. The storage required for excess digits is given by the following table.

Leftover Digits Number of Bytes
0 0
1 1
2 1
3 2
4 2

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

So that implies that a DECIMAL(12,4) would require:

8 bytes for integer portion and 2 types for the 'leftover' portion for total 10 bytes.

So 1st question is, wouldn't DECIMAL(18,4) use the same 10 bytes of storage? If I want to save storage, I would need to bump down to DECIMAL(9,4) and that's not an option for me.

IF so, 2nd question any idea if mysql processes DECIMAL(12,4) more efficiently (internally) than DECIMAL(18,4)? I dont think that question is necessarily answerable, but thought I would give it a shot! maybe someone has done some sort of benchmark...

thx.

Don

Upvotes: 3

Views: 5063

Answers (3)

theking2
theking2

Reputation: 2803

It can be calculated with this function:

DELIMITER $$
CREATE FUNCTION `fn_DECIMAL_SIZE`(`M` INT, `D` INT) RETURNS int(11)
    DETERMINISTIC
BEGIN
set @m = m;
set @d = d;


set @i = @m-@d;

set @size =         4 * floor( @i/9 );
set @size = @size + ceil( (@i % 9) / 2 );

set @size = @size + 4 * floor( @d/9 );
set @size = @size + ceil( (@d % 9) / 2 );

return @size;
END$$
DELIMITER ;

source

to create a list of some values use:

select m, d, fn_DECIMAL_SIZE(m,d)
from (
    (select seq as m from seq_1_to_20) _m
    join
    (select seq as d from seq_0_to_6) _d
)
where m>d;

Upvotes: 0

StudyOfCrying
StudyOfCrying

Reputation: 530

Without thinking too much about it, I believe ybercube has the correct answer. Having said that, couldn't you just go ahead and easily test this yourself by creating a table and doing some inserts? "show table status" should probably have the information you need.

Upvotes: -3

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

You have your calculations wrong.

If I understand correctly what the page you link to describes, a DECIMAL(12,4) would require:

The integer portion is 8 digits, so 4 bytes

The fractional part is 4 digits, so 2 bytes.

Total is 6 bytes.

For DECIMAL(18,4), which has 14 integer digits and 4 fractional digits, it would require (4+3)+(2) = 9 bytes.

Upvotes: 6

Related Questions