Mysql decimal type performance by digits setting

All we need is an easy explanation of the problem, so here it is.

I read online doc about Mysql decimal type size:

Leftover Digits Number of Bytes
0 0
1–2 1
3–4 2
5–6 3
7–9 4

So we know that digits number 7~9 occupied same size in disk, but how about performance? Is 7 digits same as 9 digits or better?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

DECIMAL is implemented by a library that assumes that 2 digits are packed per byte or 9 digits per 32-bit integer. 7 digits won’t fit in 3 bytes; 9 will fit in 4 bytes. The library allows up to 64 (or maybe only 63) digits — a quite huge value.

In DECIMAL(M,N), the data is limited based on M and N. Whether it is limited to 7 or 9 should make no difference in performance. Even if it does, the effort is very small compared to bigger issues, such as fetching the row.

Tip: A simple Rule of Thumb: DECIMAL(M,N) takes about M/2 bytes.

DECIMAL(11,2) for money allows up to a billion dollars/Euros/etc in 5 bytes. I see no advantage in using (10,2) or (9,2), each of which is also 5 bytes.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply