January 24, 2018

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!
About Shiv Iyer 12 Articles
Independent and vendor neutral MySQL, MariaDB and Percona Server consultant with core expertise in performance, scalability and high availability. Shiv is a frequent speaker in Open Source Software conferences worldwide. Shiv currently is the founder and principal of MinervaSQL, An boutique private-label MySQL, MariaDB and Percona Server consulting and remote DBA services company with several customers worldwide. Shiv in the past worked for companies like MySQL AB, SUN, Oracle, AOL, Nike, eBay, PayPal, PalominoDB (acquired by Pythian) and Percona in Sr. MySQL consulting and professional services roles. To engage MinervaSQL for MySQL consulting and professional services, please send email to shiv@minervasql.com