3

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

Improve this question

3 Answers 3

Reset to default
6

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I interpreted the documentation saying "Each multiple of nine digits requires four bytes" to suggest that allocation is done in multiples of 4 bytes. so that 9 integer digits would require 4 bytes, and if you go to 10 integer digits the storage jumps up to 8 bytes. meaning, the increment is 4 bytes. If I am wrong on this, that would be helpful information to know thank you for the response!
Oh and by the way I see my error in calculating storage for DECIMAL(12,4) you are indeed correct it is 6 bytes..
0

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;
Improve this answer

Comments

-3

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.

Improve this answer

4 Comments

Not sure if 'show table status' is a way to deterministically see the size of table rows. there is a column "Avg_row_length" which might be it.. I guess it is called an average in case there are varchar() columns in the table. perhaps for a table that has no varchar() in it, then the "Avg" is not an average - I will do some tests as you suggest Thx.
I'm just suggesting starting with an empty table, insert a single row filling all possible data to the maximum and show table status should show you the size of that row (i.e. the only row in the table). Maybe I'm mistaken?
You're right! this worked perfectly. I experimented creating tables with different DECIMAL(x,y) columns, inserted data and used 'show table status' to see what the length was. Thanks!
@DonWool Did you get some results from your experiments?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.