(mysql.info) dynamic-format

 
 14.1.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 non-contiguous 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). Note that 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.  Non-empty
      strings are saved as a length byte plus the string contents.
 
    * Much less disk space usually is required than for fixed-length
      tables.
 
    * Each row uses only as much space as is required. However, if a row
      becomes larger, it is split into as many pieces as are required,
      resulting in row fragmentation. For example, if you update a row
      with information that extends the row length, the row becomes
      fragmented. In this case, you may have to run `OPTIMIZE TABLE' or
      `myisamchk -r' from time to time to improve performance. Use
      `myisamchk -ei' to obtain table statistics.
 
    * More difficult than static-format tables to reconstruct after a
      crash, because rows may be fragmented into many pieces and links
      (fragments) may be missing.
 
    * The expected row length for dynamic-sized rows is calculated using
      the following expression:
 
           3
           + (NUMBER OF COLUMNS + 7) / 8
           + (NUMBER OF CHAR COLUMNS)
           + (PACKED SIZE OF NUMERIC COLUMNS)
           + (LENGTH OF STRINGS)
           + (NUMBER OF NULL COLUMNS + 7) / 8
 
      There is a penalty of 6 bytes for each link. A dynamic row is
      linked whenever an update causes an enlargement of the row. Each
      new link is at least 20 bytes, so the next enlargement probably
      goes in the same link. If not, another link is created. You can
      find the number of links using `myisamchk -ed'. All links may be
      removed with `OPTIMIZE TABLE' or `myisamchk -r'.
Info Catalog
(mysql.info) static-format
(mysql.info) myisam-table-formats
(mysql.info) compressed-format
automatically generated byinfo2html