High Performance MySQL [Ch.4: Optimizing Schema and Data Types]

Three rules for choosing a type

• Smaller is usually better. Pick the smallest type that holds every value you will realistically store. Every byte is repeated per row, per index, and in memory. Leave room for growth you can actually foresee, but not for fantasies, because widening a column on a big table is expensive.
• Simple is good. Integers compare faster than strings, which carry a character set and collation. An IPv4 address is a 32-bit number in disguise: INET_ATON() and INET_NTOA() convert between the two forms.
• Avoid NULL where you can. Nullable columns complicate indexes, statistics and comparisons. Changing an existing schema just for this rarely pays off; declaring new columns NOT NULL by default does.

Choose the general class first (number, string, temporal), then the specific type within it, comparing range, precision and size.

Numbers

Whole numbers use TINYINT, SMALLINT, MEDIUMINT, INT or BIGINT: 1, 2, 3, 4 and 8 bytes. UNSIGNED drops negatives and doubles the positive range at the same size. The display width in INT(11) never changed storage and is deprecated in MySQL 8.

For fractions, FLOAT (4 bytes) and DOUBLE (8 bytes) are approximate; DECIMAL is exact and costs more space and CPU. Use exact types for money. A common trick for currency is storing integer cents in a BIGINT.

Strings

Generous maximum lengths are not free: when MySQL builds in-memory temporary tables or sort buffers, it may allocate space for the declared size, not the actual one.

Dates and times

DATETIME covers years 1000 to 9999 and stores the value as written. TIMESTAMP stores seconds since the Unix epoch in UTC, converts to and from the session time zone, and ends in 2038. Both accept fractional seconds, for example DATETIME(3) for milliseconds. A common, simple convention is to store UTC in DATETIME and do time-zone logic in the application.

For flags and bit sets, BIT and SET pack booleans compactly, at the cost of being awkward to query.

Choosing identifiers

• Same type on both sides of a join. Mixing INT and BIGINT UNSIGNED, or numbers and strings, forces conversions and can disable index use.
• Integers with AUTO_INCREMENT are the default choice: small, fast to compare, and appended in order to the clustered index.
• Avoid ENUM and SET as keys, and avoid long strings, which are slow to compare and make every secondary index fatter.
• Random keys such as UUIDs insert into random places in the B-Tree: page splits, fragmentation and a cold buffer pool. If you need them, store them as BINARY(16) (MySQL 8's UUID_TO_BIN(uuid, 1) also reorders the time bits so inserts stay roughly sequential).

MySQL-specific schema gotchas

• Very wide tables. The server and InnoDB exchange rows in a buffer that has to be decoded column by column. Hundreds of columns make that conversion show up as CPU.
• Too many joins. Entity-attribute-value designs turn every read into a pile of self-joins. MySQL caps a join at 61 tables, and performance degrades long before that.
• ENUM as a moving target. If the value list changes often, a lookup table is easier to evolve.
• NULL versus magic values. Avoid needless NULLs, but do not replace a genuine "unknown" with sentinel values like 0 or '1970-01-01' that queries then have to special-case.

Normalized or denormalized

A normalized schema stores each fact once. Updates touch one row, tables are narrow and fit in memory, and there is no need for DISTINCT to undo duplication. The price is joins on almost every read.

A denormalized schema copies data to where it is read. Reads skip joins, and, often more importantly, filters and sort columns can live in one table and share one index. Suppose a bug tracker lists the latest bugs from paying customers. With plan only on the customers table, MySQL must either walk bugs by date and check each customer, or fetch all paying customers' bugs and sort them. Copy plan onto the bugs table and an index on (plan, created_at) answers the query directly.

The usual answer is a mix: normalize, then duplicate the few columns hot reads need, and keep them in sync in the application or with triggers. Duplication is cheapest for values that rarely change.

Cache and summary tables

When some staleness is acceptable, build separate tables shaped for reading. A cache table holds denormalized copies of other tables' data; a summary table holds precomputed aggregates, such as bugs per project per day, refreshed on a schedule. Reads become cheap lookups, and the cost moves to a background job. Counter tables, sometimes split over several rows to avoid lock contention on one hot row, are a close relative.

Nothing is ever final

Schemas change, and on large tables changing them is the expensive part. Many ALTER TABLE operations in MySQL 8 run in place or instantly (adding a column is often ALGORITHM=INSTANT), but others still rebuild the table by copying every row, needing up to twice the disk and hours of time. For those, teams use online schema change tools (pt-online-schema-change, gh-ost) that build a shadow table and swap it in, or apply the change on replicas first and fail over.

Common questions