Menu

Post image 1
Post image 2
1 / 2
0

Cutting a MySQL table in half with one line

DEV Community·Andreas Bergström·28 days ago
#lnTHNxnw
Reading 0:00
15s threshold

We had a wide MySQL table — 8 million rows, 8 GB on disk, mostly index — about to grow 10× from a historical backfill. The instinct was to normalise: pull repeated strings into dimension tables. Classic relational hygiene. But twenty minutes spent measuring cardinality and average length per column pointed somewhere different. The biggest single column by raw bytes was a high-cardinality, near-unique click identifier from ad platforms. Normalising it would have moved the bytes around without removing them. Meanwhile InnoDB's ROW_FORMAT=COMPRESSED finds patterns within strings — particularly devastating on indexed URL-shaped columns — and it ships in a one-line ALTER TABLE with ALGORITHM=INPLACE, LOCK=NONE , no application code changes, fully reversible. Result on the production table: 8.18 GB → 3.63 GB, with the index side compressing 59%.…

Continue reading — create a free account

Join HashtagPLUS to read full articles, follow hashtags, vote, and join the conversation.

Read More