MyISAM Table Storage Formats Static-format tables have these characteristics: • CHAR  and  VARCHAR  columns are space-padded to the specified column width, although the column type is not altered.  BINARY  and  VARBINARY  columns are padded with  0x00  bytes to the column width. • NULL  columns require additional space in the row to record whether their values are  NULL . Each  NULL column takes one bit extra, rounded up to the nearest byte. •Very quick. •Easy to cache. •Easy to reconstruct after a crash, because rows are located in fixed positions. •Reorganization is unnecessary unless you delete a huge number of rows and want to return free disk space to the operating system. To do this, use  OPTIMIZE TABLE  or  myisamchk -r . •Usually require more disk space than dynamic-format tables. •The expected row length in bytes for static-sized rows is calculated using the following expression: row length = 1 + ( sum of column lengths ) + ( number of NULL columns  +  delete_flag  + 7)/8 + ( number of variable-length columns ) delete_flag  is 1 for tables with static row format. Static tables use a bit in the row record for a flag that indicates whether the row has been deleted.  delete_flag  is 0 for dynamic tables because the flag is stored in the dynamic row header. 16.2.3.2 Dynamic Table Characteristics Dynamic storage format is used if a  MyISAM  table contains any variable-length columns ( VARCHAR , VARBINARY ,  BLOB , or  TEXT ), or if the table was created with the  ROW_FORMAT=DYNAMIC  table option. Dynamic format is a little more complex than static format because each row has a header that indicates how long it is. A row can become fragmented (stored in noncontiguous pieces) when it is made longer as a result of an update. 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. Dynamic-format tables have these characteristics: •All string columns are dynamic except those with a length less than four. •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. • NULL  columns require additional space in the row to record whether their values are  NULL . Each  NULL column takes one bit extra, rounded up to the nearest byte. •Much less disk space usually is required than for fixed-length tables. 3407