database
Schema Optimization and Indexing
Whole numbers
TINYINT -- 8 bits
SMALLINT -- 16 bits
MEDIUMINT -- 24 bits
INT -- 32 bits
BIGINT -- 64 bits
Store value from - 2 ** (N-1)
to 2 ** (N-1) - 1
SIGNED
andUNSIGNED
:TINYINT UNSIGNED
store values from 0 - 255 instead of -128 - 127 asTINYINT
- Your choise determines how MySQL store values in memory, disk, but, MySQL use
BIGINT
64 bits in computations, even on 32 bits architect. - MySQL let you define “width”, example INT(11), which defines the number of characters MySQL’s interactive tools, display purpose, doesn’t restrict the range of value: INT(1) and INT(11) are identical.
Real numbers
FLOAT
,DOUBLE
types support approximate calculations with standard floating-point math.DECIMAL
types is for storing exact fractional numbers.- MySQL 5.0 or newer, supports exact math on
DECIMAL
, MySQL 4.1 or earlier used floating-point match to perform calculation on DECIMAL values. - MySQL 5.0 or newer itself performs
DECIMAL
exact math, because CPUs don’t support compuations directly, floating-point math is faster because CPUs natively supports.
Precision
DECIMAL
columns, you can specify maximum allowed digits before and after decimal point -> influences the column’s space consumption- MySQL 5.0 or newer pack the digits into a binary string (9 digits per 4 bytes): Ex:
DECIMAL(18,9)
will store 9 digits from each side, 4 bytes for 9 digits before decimal point, 1 byte for decimal point, 4 bytes for 9 digits after decimal point. DECIMAL
number in MySQL 5.0 or newer can have up to 65 digits, earlier MySQL version had a limit 254 bits and store values as unpacked string (one by per digits), useDOUBLE
for computation.DECIMAL
use cases- Floating-point types typically use less space than
DECIMAL
,FLOAT
use 4 bytes,DOUBLE
uses 8 bytes. - MySQL uses
DOUBLE
for computations DECIMAL
require space and computational costs -> use when you need exact results for fractical numbers: ex: storing financial data