Master MySQL Data Types and Indexes to Boost Performance

MySQL Numeric and Temporal Types

Numeric types differ in range and storage size. Choosing the smallest type that fits reduces row size (max 65,535 bytes) and CPU/memory/I/O load.

Supported integer types and bytes:

TINYINT – 1 byte

SMALLINT – 2 bytes

MEDIUMINT – 3 bytes

INT – 4 bytes

BIGINT – 8 bytes

Floating‑point types:

FLOAT – 4 bytes

DOUBLE – 8 bytes

DECIMAL – storage depends on precision/scale; used for exact arithmetic.

Temporal types:

DATE: ‘1000‑01‑01’ to ‘9999‑12‑31’

DATETIME: ‘1000‑01‑01 00:00:00’ to ‘9999‑12‑31 23:59:59’

TIMESTAMP: ‘1970‑01‑01 00:00:01’ UTC to ‘2038‑01‑19 03:14:07’ UTC (smaller storage, highest efficiency).

String Types and Storage

MySQL provides CHAR, VARCHAR, TEXT, BINARY, VARBINARY, BLOB and their variants. Storage depends on character set and length definitions.

CHAR(M): fixed‑length, padded with spaces to M characters; M ≤ 255. Stored inline.

VARCHAR(M): variable‑length, stores actual bytes plus 1–2 byte length prefix; M up to 65,535 bytes (subject to row size). No padding.

BINARY(M)/VARBINARY(M): raw byte storage; BINARY pads with 0x00, VARBINARY stores as‑is.

TEXT/BLOB families (TINY, MEDIUM, LONG): off‑row storage; row contains a 1–4 byte pointer plus length. No DEFAULT values; indexing requires a prefix length.

For frequently accessed short strings, VARCHAR is generally faster than TEXT because the data resides inline with the row.

Choosing String Types

Use CHAR for fixed‑length short values.

Use VARCHAR for variable‑length data, respecting the 65,535‑byte row limit.

Use BINARY/VARBINARY for binary data where character‑set semantics are irrelevant.

Avoid indexing TEXT/BLOB columns unless a prefix length is specified.

MySQL Index Types and Best Practices

Supported index types: PRIMARY KEY, UNIQUE, INDEX (non‑unique), FULLTEXT (not covered here). Index columns must be plain column names.

For BLOB/TEXT or long VARCHAR columns, specify a prefix length (max 767 bytes in InnoDB).

Order columns in multi‑column indexes left‑to‑right according to query patterns; the optimizer stops using columns after a range condition.

Do not over‑index: each index adds storage overhead and slows INSERT/UPDATE/DELETE. Evaluate selectivity (unique values / total rows) before creating an index.

PRIMARY KEY and Clustered Index

In InnoDB, the PRIMARY KEY is a clustered index; rows are stored in primary‑key order. If no natural key exists, use an auto‑increment integer surrogate key for optimal insert order and foreign‑key performance.

UNIQUE Index

Enforces uniqueness for a column or column combination (NULLs allowed). Acts both as an index and a data‑integrity constraint.

B+Tree vs HASH Indexes

B+Tree indexes store rows in sorted order, supporting equality, range, ORDER BY, GROUP BY, and left‑most prefix queries (e.g., LIKE 'abc%').

HASH indexes are faster for pure equality lookups but cannot be used for ORDER BY, range scans, or partial column matches; the full indexed column must be specified.