Different Between MyISAM And InnoDB In MySQL

Server environment

1
2
3
4
5
$ cat /etc/issue
Ubuntu 14.04.5 LTS

$ mysqld -V
mysqld Ver 5.5.58-0ubuntu0.14.04.1 for debian-linux-gnu on x86_64 ((Ubuntu))
Diff MyISAM InnoDB
Default False True
Storage Structure Each table will be saved in three file.
frm: Defined Table
MYD: Data file
MYI: Index File
All table will be saved in the same data file(Maybe in multi files).
The table size is limited by system file size.It is 2GB generally.
Storage Space Can be compressed.
The space size is less than InnoDB.
For high speed caching data and indexes,
Tables in InnoDB need more memory and space
to build its dedicated buffer pool in main memory.
Transaction Safe Not Support Support.
Safe Transaction Table includes
commit 、rollback and crash recovery methods.
COUNT without WHERE Better
MyISAM has saved the table row number.
It need to scan all rows to count
Lock Only Table Lock Table Lock and Row Lock
(The row lock is only work that
using primary key in the where condition
, otherwise the table will be locked until the write operation finished)
Foreign Key Not Support Support
FULLTEXT Index Support Not Support (But you can use Sphinx to do this)

In many cases, Lots of select operation in MyISAM is a better choise. Because

  • InnoDB will cache data block but the MyISAM only cache data index block which can reduce the number of times that data swapped in and out of memory
  • InnoDB positioning addressing must mapping to block then to row. MyISAM records the file offset then direct the position so it will faster than InnoDB.
  • InnoDB also needs to maintain MVCC(Multi Version Concurrency Control) consistency. Although you haven’t use it, but it still needs to check and maintain.
Share