Search code examples
mysqlmyisam

Understanding MySQL MyISAM ROW_FORMAT=DYNAMIC

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?

Solution

  • 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.