MySQL: Change compression on an existing InnoDB table

Question

Is there any way to turn on compression for an existing table? I don't care if there's downtime for this.

The docs only mention setting compression when the table is created.

I'd just try an ALTER TABLE ... FORCE once the new settings have been changed. — danblack, Feb 3, 2019 at 22:27
What new settings? I'm asking if I have a normal (uncompressed) table, how would I compress it e.g. on console. — mahemoff, Feb 3, 2019 at 22:37

danblack · Accepted Answer · 2019-02-03 23:10:27Z

4

ALTER TABLE xxx ROW_FORMAT=COMPRESSED

answered Feb 3, 2019 at 22:41

danblack

7,6492 gold badges10 silver badges27 bronze badges

ALTER TABLE xxx ROW_FORMAT=Compressed apparently works too, mentioned here lullabot.com/articles/…. Thanks.
– mahemoff
Feb 3, 2019 at 23:09
1

Ack, was getting confused about if you wanted removing or adding. More coffee required.
– danblack
Feb 3, 2019 at 23:10
@danblack - DYNAMIC is not compressed, but otherwise very similar.
– Rick James
Feb 4, 2019 at 0:58
@mahemoff - Please report on how much smaller the table is (see SHOW TABLE STATUS). The ratios I have seen have been 2:1 compression.
– Rick James
Feb 4, 2019 at 0:59
1

Just to weigh in with my own compression stats: 3 fields (int, json, datetime), avg row length 76KB, 1.4M rows. Went from 105GB to 23.6GB.
– The Onin
Aug 18, 2021 at 15:34

| Show 6 more comments

1 Answer 1