Storing numbers wisely in MySQL

Generally we have two kinds of numbers, Whole numbers and real numbers (numbers with Fractional units). When we are using whole numbers the natural choice is TINYINT, SMALLINT, MEDIUMINT, INT and BIGINT. These datatypes require 8,16,24,32,64 bits of storage space accordingly.
MySQL allows you to specify “width” of integer types, like INT(25). This doesn’t mean /restrict legal range of values but actually specifies the number of characters MySQL’s interactive tools will reserve for display purposes, INT(1) is same as INT(20) for storage and computational purposes. 
Real numbers can have both fractional and decimal numbers which are very large and don’t fit in “BIGINT”. The “FLOAT” and “DOUBLE” support approximate calculation with standard floating point math.
The “DECIMAL” type is good for storing exact fractional numbers. From MySQL 5.0 and newer, the “DECIMAL” type support exact math! Due to lower precision level “FLOATING-POINT” used during MySQL 4.1 days used to give weird result..
“FLOATING-POINT” math is much faster because CPU performs computations natively. “FLOATING-POINT” type typically use less space than “DECIMAL” to store same range of values. MySQL uses “DOUBLE” for its internal calculations on “FLOATING-POINT” types dues to  its greater precision compared to “FLOAT"

Considering expensive computational cost you should choose “DECIMAL” only when you need exact results for fractional numbers, for example, financial data. It makes more sense to use “BIGINT” for high volume transactions which are not so critical or store them in multiples of smallest fractions of currency! 

No comments: