MyISAM

by | Nov 19, 2015 | Storage engines

  • default in 5.1 or older
  • main features:
    • full-text indexing
    • compression
    • GIS spacial functions
  • does NOT have transactions or row-level locking
  • typically stores data in 2 files – data file, index file
  • tables can have rows both with fixed or dynamic length
  • size of table is limited by OS
  • locks:
    • locks entire table
    • concurrency inserts allow to add new row even when there is shared read lock on table
  • table repair:
    • commands CHECK TABLE and REPAIR TABLE – simply deletes corrupted data from table
    • if server is offline we can use also “myisamchk” command line tool
  • indexes:
    • up to first 500 characters on BLOB or TEXT
    • delayed key writes – indexes are updated only in memory and flushed to disk only during buffer prune – if server crashes then index must be repaired
  • data compression:
    • we cannot modify compressed tables – therefore usable for huge read-only data
    • helps reduce I/O, overhead on CPU is unsignificant