Bug #22050 dynamic row format for nullable integer columns in myisam
Submitted: 6 Sep 2006 12:00 Modified: 8 Sep 2006 10:42
Reporter: Martin Friebe (Gold Quality Contributor) (OCA) Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: MyISAM storage engine Severity:S4 (Feature request)
Version:* OS:Any (*)
Assigned to: CPU Architecture:Any
Tags: dynamic row length, myisam, null

[6 Sep 2006 12:00] Martin Friebe
My understanding of the myisam row format is:

a varchar allocates at least 1 byte/word for the length, and optional additional bytes for the content

an integer (similiar for bigint / smallint) always alocates 4 bytes

additionally a bit is reserved to indicate null values, if the column is nullable.

a varchar that is null, will then contain just a length? or is it removed completly?

I have monitored the grwoth of a myisam table (show table status) and it is equal for "variable length records" independent on nulls or defined values have been inserted

I believe that myisam varible length rows still store a data entry for values that are null? 

If that is right, this data is not needed, and would not need to be stored.

If I am wrong about data being stored for null values, please accept my apologies, and close this report

How to repeat:

Suggested fix:
on tables with variable row length, do not allocate space for null values.

allow a way to specivy if an nullable integer in an otherwise fixed-row-length scenario should cause variable row length
[8 Sep 2006 10:42] Valeriy Kravchuk
Thank you for a reasonable feature request. Looks like you are rignt, based on http://dev.mysql.com/doc/internals/en/myisam-introduction.html, for example. 

Oracle, for example, does not store _trailing_ NULL columns of any data type, at least. So, some optimizations in NULL values storage will be reasonable.