MyISAM vs InnoDB


MyISAM versus InnoDB

Transactions, referential integrity, concurrency, and Storage limits are the main difference between MyISAM & InnoDB.


Features MyISAM InnoDB
Storage limits 256TB 64TB
Transactions No Yes
Locking granularity Table Row
Geospatial data type support Yes Yes
Geospatial indexing support Yes Yes [aa]
B-tree indexes Yes Yes
vT-tree indexes No No
Hash indexes No No [bb]
Full-text search indexes Yes Yes [cc]
Clustered indexes No Yes
Data caches No Yes
Index caches Yes Yes
Compressed data Yes [a] Yes [dd]
Encrypted data [b] Yes Yes
Cluster database support No No
Replication support[c] Yes Yes
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
Deadlocks 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

Operations Benefits
– 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.

Performance Benefits
– Clustering by primary key
– Caching data
– Higher concurrency
– Background flushes

Performance Drawbacks
– Large table size
– Slower writes
– Slower blob handling
– Concurrency issues
– Slow data load
– COUNT(*) without where is show stopper

Operations Drawbacks
– 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

About This Author

I'm a software engineer with many years of experience, open source enthusiast, now I'm creating and contributing to awesome PHP web projects. I love coding as much as learning, and I enjoy trying new languages and patterns. My passion revolves around (but is not limited to) back-end development.

Comments are closed