1

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?

guigoz
  • 674
  • 4
  • 21
  • Does this answer your question? [MySQL Row Format: Difference between fixed and dynamic?](https://stackoverflow.com/questions/147315/mysql-row-format-difference-between-fixed-and-dynamic) – nbk Sep 01 '21 at 15:58
  • @nbk None of the answers there explain why it would be different with no variable-length columns. – Barmar Sep 01 '21 at 16:09
  • Have you done `OPTIMIZE TABLE` on both versions of the table? You may be seeing space taken up by deleted rows. – Barmar Sep 01 '21 at 16:10
  • @nbk No, I already read this answer before posting my question. My question is about the physical binary file sizes which should be the same in both `ROW_FORMAT` since all the columns have fixed lengths – guigoz Sep 01 '21 at 16:11
  • @Barmar there are no deleted rows – guigoz Sep 01 '21 at 16:13
  • How are you determining the file size? Unix files have a length and number of blocks. The latter can be less than `length/blocksize` if there are zero blocks, because they're not stored explicitly on disk. So if you have lots of contiguous all-zero rows, that might get optimized out of disk space. – Barmar Sep 01 '21 at 16:16
  • @Barmar expected file size = (sum of column lengths) * (row count) = 3GB – guigoz Sep 01 '21 at 16:19
  • I was asking what ACTUAL file size you're using. Size in bytes or size in disk blocks? – Barmar Sep 01 '21 at 16:19
  • @Barmar I'm using the size in bytes, which matches the size given by phpmyadmin – guigoz Sep 01 '21 at 16:22

1 Answers1

3

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.

Barmar
  • 741,623
  • 53
  • 500
  • 612