MyISAM versus InnoDB
Transactions, referential integrity, concurrency, and Storage limits are the main difference between MyISAM & InnoDB.
|Geospatial data type support||Yes||Yes|
|Geospatial indexing support||Yes||Yes [aa]|
|Hash indexes||No||No [bb]|
|Full-text search indexes||Yes||Yes [cc]|
|Compressed data||Yes [a]||Yes [dd]|
|Encrypted data [b]||Yes||Yes|
|Cluster database support||No||No|
|Foreign key support||No||Yes|
|Backup / point-in-time recovery [c]||Yes||Yes|
|Query cache support||Yes||Yes|
|Update statistics for data dictionary||No||Yes|
[a] Compressed MyISAM tables are supported only when using the compressed row format. Tables using the compressed row format with MyISAM are read only.
[b] Implemented in the server (via encryption functions), rather than in the storage engine.
[c] Implemented in the server, rather than in the storage engine.
[aa] InnoDB support for geospatial indexing is available in MySQL 5.7.5 and higher.
[bb] InnoDB utilizes hash indexes internally for its Adaptive Hash Index feature.
[cc] InnoDB support for FULLTEXT indexes is available in MySQL 5.6.4 and higher.
[dd] Compressed InnoDB tables require the InnoDB Barracuda file format.
Key Advantages of MyISAM
– Fast COUNT(*)s (when WHERE, GROUP BY, or JOIN is not used)
– Full text indexing
– Smaller disk footprint
– Very high table compression (read only)
– Supports concurrent inserts
– Full Text Search
– BLOB and TEXT columns can be indexed
– Copying binary tables between the servers
Key Advantages of InnoDB
– ACID transactions
– Row-level locking
– Foreign key constraints
– Automatic crash recovery
– Table compression (read/write)
– Spatial data types (no spatial indexes)
– Tables arrange your data on disk to optimize queries based on primary keys.
– Has been designed for CPU efficiency and maximum performance when processing large data volumes.
– Clustering by primary key
– Caching data
– Higher concurrency
– Background flushes
– Large table size
– Slower writes
– Slower blob handling
– Concurrency issues
– Slow data load
– COUNT(*) without where is show stopper
– Can NOT copy binary tables between the servers
– Using mysqldump as a backup may well work for MyISAM but will start taking way too much time to do restore for Innodb