Updated row format not showing?

Question

I updated row format with the following command:

ALTER TABLE my_table
    drop column id,
    add primary key(column_1, column_2),
    ROW_FORMAT = COMPRESSED,
    ALGORITHM=INPLACE,
    LOCK=EXCLUSIVE;

Ignore the drop column and add primary key clauses, it's a big table and I wanted to do that in 1 alter table...

In any case, when it completed I checked the results with:

SHOW TABLE STATUS IN my_db;

Oddly enough, in the given results under the row_format column, it shows Compact. However in the Create_options column it says row_format=COMPRESSED.

Below query shows that the row format is still showing Compact.

SELECT NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES;

My question is why did the row format not update? What does it mean that the create options now says row_format=COMPRESSED even though the row format on the table shows Compact?

Note: I updated row format to compressed just to see if it would improve performance. There are two int(11) columns and one decimal(18,6). Now that I think about it, does row_format=compressed not apply for a table without strings?

Answer 1

does row_format=compressed not apply for a table without strings

There are other things in the row -- lengths, offsets, etc.

Each INT takes 4 bytes; that DECIMAL takes 9. That's 17 bytes, but I would expect Avg_row_length to be more than 40 due to the overhead. And, as you may have found out, Compression would not help much.

There are smaller INTs and that DECIMAL seems excessively big. Consider whether a 4-byte FLOAT (about 7 signifigant digits) would suffice.

If you wish to tell us what the numbers represent, I may have further tips for saving space.

Even with strings, row_format=COMPRESSED rare gets more than 2:1 compression.