MySQL Bugs: #30824: COLUMN_FORMAT=FIXED columns in ROW

Bug #30824	COLUMN_FORMAT=FIXED columns in ROW_FORMAT=DYNAMIC MyISAM tables?
Submitted:	5 Sep 2007 10:20	Modified:	4 Feb 2009 10:22
Reporter:	Serdar S. Kacar	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S4 (Feature request)
Version:	5.X	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	column_format, myisam, ROW_FORMAT=DYNAMIC

Description:
One of the dynamic-format tables problems is fragmentation.
Sometimes it is inevitable, but sometimes definitely evitable. 

Consider:
- a dynamic MyISAM table (say TEXT/BLOB columns are needed), 
- there are many INTEGER, DATETIME and similar columns that are good candidates
  for Zero-Value-Compression.
- Finally, in the row life time, say, 
  TEXT/BLOB are quite static,
  Many numeric and datetime type columns are initialized by 0/'0000-00-00'
    and later, on one-or-more updates, take non-zero values.

Then Zero-Value-Compression imposes high risk of fragmentation at each zero to non-zero update.

An obvious workaroud is always (insert/update) storing non-zero values on such columns. But that solution 
- does not look pretty,
- require special handling by the upper level application, and
- broke cross-compatibility.

How to repeat:
N/A

Suggested fix:
Let COLUMN_FORMAT in column definitions be effective for all 
(at least some, including MyISAM) storage engines.

Then the only job to be done is about storage engines' ROW-DATA-INTEGRATORs 
(a part that compile row data for storage, I do not know MySQL internal name.)
They should skip zero value compression if the column is defined as
COLUMN_FORMAT=FIXED, and allocate space/store whatever the value is.

If this feature is implemented, besides COLUMN_FORMAT definition part,
the following content has to be changed in documents :

14.4. The MyISAM Storage Engine
14.4.3.2. Dynamic Table Characteristics

Old:
...
You can use OPTIMIZE TABLE or myisamchk -r to defragment a table. If you have fixed-length columns that you access or change frequently in a table that also contains some variable-length columns, it might be a good idea to move the variable-length columns to other tables just to avoid fragmentation. 
...

New:
...
You can use OPTIMIZE TABLE or myisamchk -r to defragment a table. If you have fixed-length columns that you access or change frequently in a table that also contains some variable-length columns, it might be a good idea to move the variable-length columns to other tables just to avoid fragmentation. Alternatively, if the Zero-Value-Compression is the primary source of fragmentation, you can consider COLUMN_FORMAT=FIXED attribute for the fixed-length columns.
...

Many thanks for writing a feature request.

Our development will discuss this.