First major difference I see is that InnoDB implements row-level lock while MyISAM can do only a table-level lock. You will find better crash recovery in InnoDB. However, it doesn’t have
FULLTEXT search indexes until v5.6, as does MyISAM. InnoDB also implements transactions, foreign keys and relationship constraints while MyISAM does not.
The list can go a bit further. Yet, they both have their unique advantages in their favor and disadvantages against each other. Each of them is more suitable in some scenarios than the other.
So to summarize (TL;DR):
- InnoDB has row-level locking, MyISAM can only do full table-level locking.
- InnoDB has better crash recovery.
- MyISAM has
FULLTEXTsearch indexes, InnoDB did not until MySQL 5.6 (Feb 2013).
- InnoDB implements transactions, foreign keys and relationship constraints, MyISAM does not.
Another major difference not as yet mentioned is how caching for each storage engine is done.
The main mechanism used is the key cache. It only caches index pages from .MYI files. To size your key cache, run the following query:
SELECT CONCAT(ROUND(KBS/POWER(1024, IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999), SUBSTR(' KMG',IF(PowerOf1024<0,0, IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_key_buffer_size FROM (SELECT LEAST(POWER(2,32),KBS1) KBS FROM (SELECT SUM(index_length) KBS1 FROM information_schema.tables WHERE engine="MyISAM" AND table_schema NOT IN ('information_schema','mysql')) AA ) A, (SELECT 2 PowerOf1024) B;
This will give the Recommended Setting for MyISAM Key Cache (key_buffer_size) given your current data set (the query will cap the recommendation at 4G (4096M). For 32-bit OS, 4GB is the limit. For 64-bit, 8GB.
The main mechanism used is the InnoDB Buffer Pool. It caches data and index pages from InnoDB tables accessed. To size your InnoDB Buffer Pool, run the following query:
SELECT CONCAT(ROUND(KBS/POWER(1024, IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.49999), SUBSTR(' KMG',IF(PowerOf1024<0,0, IF(PowerOf1024>3,0,PowerOf1024))+1,1)) recommended_innodb_buffer_pool_size FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables WHERE engine="InnoDB") A, (SELECT 2 PowerOf1024) B;
This will give the Recommended Setting for the size of the InnoDB Buffer Pool (innodb_buffer_pool_size) given your current data set.
Don’t forget to resize the InnoDB Log Files (ib_logfile0 and ib_logfile1). MySQL Source Code places a cap of the combined sizes of all InnoDB Log Files must be < 4G (4096M).
For the sake of simplicity, given just two log files, here is how you can size them:
- Step 1) Add innodb_log_file_size=NNN to /etc/my.cnf
(NNN should be 25% of innodb_buffer_pool_size or 2047M, whichever is smaller)
- Step 2)
service mysql stop
- Step 3)
- Step 4)
service mysql start(ib_logfile0 and ib_logfile1 are recreated)
At the End of both queries is a an Inline Query
(SELECT 2 PowerOf1024) B
(SELECT 0 PowerOf1024)gives the Setting in Bytes
(SELECT 1 PowerOf1024)gives the Setting in Kilobytes
(SELECT 2 PowerOf1024)gives the Setting in Megabytes
(SELECT 3 PowerOf1024)gives the Setting in Gigabytes
- No powers less that 0 or greater than 3 is accepted
There is no substitute for common sense. If you have limited memory, a mixture of storage engines, or a combination thereof, you will have to adjust for different scenarios.
- If you have 2GB RAM and 16GB of InnoDB, allocate 512M as innodb_buffer_pool.
- If you have 2GB RAM and 4GB of MyISAM Indexes, allocate 512M as key_buffer_size.
- If you have 2GB RAM and 4GB of MyISAM Indexes and 16GB InnoDB, allocate 512M as key_buffer_size and 512M as innodb_buffer_pool_size.
Possible scenarios are endless !!!
Remember, whatever you allocate for, leave enough RAM for DB Connections and the Operating System.
- ACID transactions
- row-level locking
- foreign key constraints
- automatic crash recovery
- table compression (read/write)
- spatial data types (no spatial indexes)
In InnoDB all data in a row except for TEXT and BLOB can occupy 8,000 bytes at most. Full text indexing is not available in InnoDB until MySQL 5.6 (Feb 2013). In InnoDB the
GROUP BY, or
JOIN is not used) execute slower than in MyISAM because the row count is not stored internally. InnoDB stores both data and indexes in one file. InnoDB uses a buffer pool to cache both data and indexes.
GROUP BY, or
JOINis not used)
- full text indexing (update: supported in InnoDB from MySQL 5.6)
- smaller disk footprint
- very high table compression (read only)
- spatial data types and indexes (R-tree) (update: supported in InnoDB from MySQL 5.7)
MyISAM has table-level locking, but no row-level locking. No transactions. No automatic crash recovery, but it does offer repair table functionality. No foreign key constraints. MyISAM tables are generally more compact in size on disk when compared to InnoDB tables. MyISAM tables could be further highly reduced in size by compressing with myisampack if needed, but become read-only. MyISAM stores indexes in one file and data in another. MyISAM uses key buffers for caching indexes and leaves the data caching management to the operating system.
Overall I would recommend InnoDB for most purposes and MyISAM for specialized uses only. InnoDB is now the default engine in new MySQL versions.