Re: Difference between theoretical and real storage size

[Rick James]

Since there were 10 VARCHAR NULL fields, and no other NULLable fields, I would have expected a savings of 2 bytes = (10 bits) / 8, rounded up. (Hmmm... maybe the first bit is required, and may it has only the first 7 NULLs. Or something like that. The answer is probably buried deep in the online docs. And in the code.)

The 7 was a mystery when I first encountered it. But here's the logic (again MyISAM only)...

SHOW VARIABLES LIKE 'myisam_data_pointer_size';
Gives you 6, correct? (That has been the default for the last couple of versions.)

That says that 6 bytes will be used to 'point' to a record in the .MYD file. For 'Fixed' width records, the pointer is a record number; for 'Dynamic' it is a byte offset into the .MYD file.

A pointer of that size is also used for marking off free space in the .MYD.

So...
1 byte to say free or not.
6 bytes for pointing to the next non-free spot in the file.
= 7 bytes is the minimum size for a record in the file.

All of this is quite artificial -- How often does one build a row that is only 4 bytes?
And if you really don't like the 7, you can shrink myisam_data_pointer_size, at least at the time when you create the table.

(The old default was 4, which led to false rumors that MyISAM tables were limited to 256^4 bytes (4GB).)

The max value is 7, which leads to a really, really, large limit on the size of a MyISAM table.

I'm loaded with trivia like this; got any more questions?