What are the differences between InnoDB and MyISAM?

• InnoDB has row-level locking, MyISAM can only do full table-level locking.
• InnoDB has better crash recovery.
• MyISAM has FULLTEXT search indexes, InnoDB did not until MySQL 5.6.
• InnoDB implements transactions, foreign keys and relationship constraints, MyISAM does not.

MYISAM

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 MyISAM storage engine in MySQL.

• Simpler to design and create, thus better for beginners. No worries about the foreign relationships between tables.
• Faster than InnoDB on the whole as a result of the simpler structure thus much less costs of server resources.
• Full-text indexing.
• Especially good for read-intensive (select) tables.

MyISAM Limitations:

• No foreign keys and cascading deletes/updates
• No transactional integrity (ACID compliance)
• No rollback abilities
• Row limit of 4,284,867,296 rows (232)
• Maximum of 64 indexes per row.

InnoDB

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) rm /var/log/mysql/ib_logfile[01]
• Step 4) service mysql start (ib_logfile0 and ib_logfile1 are recreated)

The InnoDB storage engine in MySQL.

• Support for transactions (giving you support for the ACID property).
• Row-level locking. Having a more fine grained locking-mechanism gives you higher concurrency compared to, for instance, MyISAM.
• Foreign key constraints. Allowing you to let the database ensure the integrity of the state of the database, and the relationships between tables.
• InnoDB is more resistant to table corruption than MyISAM.
• Support for large buffer pool for both data and indexes. MyISAM key buffer is only for indexes.
• MyISAM is stagnant; all future enhancements will be in InnoDB

InnoDB Limitations:

• No full text indexing (Below-5.6 mysql version)
• Cannot be compressed for fast, read-only