I have a MyISAM table with 1600 columns of integer type only (tinyint, smallint, mediumint, int). The table has 800k rows.
ROW_FORMAT=FIXED => binary file size = 3GB (= expected value)
ROW_FORMAT=DYNAMIC => binary file size = 200MB
Why does the file size change since all the columns (please don't question me on the column count) have fixed lengths?
Zero values are optimized in dynamic format. From the documentation:
Each row is preceded by a bitmap that indicates which columns contain the empty string (for string columns) or zero (for numeric columns). This does not include columns that contain NULL values. If a string column has a length of zero after trailing space removal, or a numeric column has a value of zero, it is marked in the bitmap and not saved to disk. Nonempty strings are saved as a length byte plus the string contents.
So the size difference suggests that most of the values in your table are zero.