I have faced a interview, This viva board team asked me, what’s main differences between MyISAM and InnoDB of MySQL engine?. I know this but forgot it this moment. So, I think , I will be write a article. Differences between as follows
InnoDB uses row level locking, has commit, rollback, and crash-recovery capabilities to protect user data.
MyISAM uses table level locking to protect user data.
Limitations of MYISAM and InnoDB as follows
• No Foriegn keys and cascading deletes and updates
• No rollback abilities
• No transactional integrity (ACID compliance)
• Row limit of 4,284,867,296 rows
• Maximum of 64 indexes per row
• No full text indexing
• Cannot be compressed for fast, read-only
MyISAM uses table level locking and this can be a great problem if your database INSERT/DELETE/UPDATE load is very high. If this is the problem then you should try converting it to INNODB. It manages non transactional tables. It has fast storage and retrieval, as well as full text searching capabilities.
When to use MyISAM?
MyISAM is designed with the idea that your database is queried far more than its updated and as a result it performs very fast read operations. If your read to write(insert|update) ratio is less than 15% its better to use MyISAM.
When to use InnoDB?
InnoDB uses row level locking, has commit, rollback, and crash-recovery capabilities to protect user data. It supports transaction and fault tolerance.