(mysql.info.gz) Dynamic format

(mysql.info.gz) MyISAM table formats
 
 14.1.3.2 Dynamic Table Characteristics
 ......................................
 
 Dynamic storage format is used if a `MyISAM' table contains any
 variable-length columns (`VARCHAR', `BLOB', or `TEXT'), or if the table
 was created with the `ROW_FORMAT=DYNAMIC' option.
 
 This format is a little more complex because each row has a header that
 indicates how long it is. One record can also end up at more than one
 location when it is made longer as a result of an update.
 
 You can use `OPTIMIZE TABLE' or `myisamchk' 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.
 
 General characteristics of dynamic-format tables:
 
    * All string columns are dynamic except those with a length less
      than four.
 
    * Each record 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 record uses only as much space as is required. However, if a
      record becomes larger, it is split into as many pieces as are
      required, resulting in record fragmentation.  For example, if you
      update a row with information that extends the row length, the row
      will be fragmented.  In this case, you may have to run `OPTIMIZE
      TABLE' or `myisamchk -r' from time to time to get better
      performance.  Use `myisamchk -ei' to obtain table statistics.
 
    * More difficult than static-format tables to reconstruct after a
      crash, because a record may be fragmented into many pieces and a
      link (fragment) may be missing.
 
    * The expected row length for dynamic-sized records 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 record is
      linked whenever an update causes an enlargement of the record.
      Each new link will be at least 20 bytes, so the next enlargement
      will probably go in the same link.  If not, there will be another
      link. You may check how many links there are with `myisamchk -ed'.
      All links may be removed with `myisamchk -r'.
Info Catalog
(mysql.info.gz) Static format
(mysql.info.gz) MyISAM table formats
(mysql.info.gz) Compressed format
automatically generated byinfo2html