Dynamic Table Characteristics

• Dynamic storage format is used if a MyISAM table contains variable length columns.
  • 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.

• 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.
  • Much less disk space is usually required than for fixed length tables.
  • Each row uses only as much space as is required.
    • If a row becomes larger, it is split into as many pieces as are required, resulting in row fragmentation.
      • Run OPTIMIZE TABLE or myisamchk -r from time to time to improve performance.
  • 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.

What are the MyISAM compressed table characteristics?

---

---

© 2007 John Michael Pierobon

Notes