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 and UNSIGNED: TINYINT UNSIGNED store values from 0 - 255 instead of -128 - 127 as TINYINT
  • 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), use DOUBLE 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