Characteristics Of MyISAM Tables

• MyISAM tables have the following characteristics:
  • All data values are stored with the low byte first.
    • There is no significant speed penalty for storing data low byte first.
    • This makes the data machine and operating system independent.
    • The only requirements for binary portability are that the machine uses two's-complement signed integers and IEEE floating-point format.
      • These requirements are widely used among mainstream machines.
      • Binary compatibility might not be applicable to embedded systems.
  • All numeric key values are stored with the high byte first to allow better index compression.
  • Large files (up to 63-bit file length) are supported on filesystems and operating systems that support large files.
  • There is a limit of 2³² (~4.295E+09) rows in a MyISAM table.
    • If MySQL is built with the --with-big-tables option, the row limitation is increased to 2⁶⁴ (1.844E+19) rows.
      • Binary distributions for Unix and Linux are built with this option.
  • The maximum number of indexes per MyISAM table is 64.
    • The maximum number of columns per index is 16.
  • The maximum key length is 1000 bytes.
    • This can be changed by changing the source and recompiling.
  • When rows are inserted in sorted order (as when using an AUTO_INCREMENT column), the index tree is split so that the high node only contains one key.
    • This improves space utilization in the index tree.
  • The data file and index file can be in different directories to get more speed.
    • Use CREATE TABLE with the DATA DIRECTORY and INDEX DIRECTORY options.
  • BLOB and TEXT columns can be indexed.
  • NULL values are allowed in indexed columns.
  • Each character column can have a different character set.

What are the MyISAM table storage formats?

---

---

© 2007 John Michael Pierobon

Notes