What are the constrains for selecting right MySQL storage engine?


MyISAM is the default storage engine for MYSQL. MyISAM is non transactional tables. This build for high performance search and retrieval of data and has full text search capability also. This is used the most in Web, data warehousing, and other application environments.
MyISAM is great for sites that have a very low INSERT/UPDATE rate and a very high SELECT rate. Very high INSERT/UPDATE queries due to the restrictions of table-level locking causing a decrease in performance.
The max number of rows supported for MyISAM is ~4.29E+09 with up to 64 indexs per table. It fields of TEXT/BLOB can also be fully indexed for cases such as searching


This is a transaction safe. This has commit, rollback, and crash-recovery capabilities to protect user data. InnoDB has row-level locking and Oracle-style consistent no locking reads increase multi-user concurrency and performance.
The ability to do row-level locking, as opposed to table-level locking, to increase performance time. This allows parallel INSERT/UPDATE/DELETE queries to be ran on the same table, unlike MyISAM where each query has to wait its turn to run.
InnoDB stores user data in clustered indexes to reduce I/O for common queries based on primary keys. Also it supports Foreign Key.
InnoDB also provides caching for data and indexes in memory, as well as on disk, which provides a large increase in performance gain. For those low on RAM this might not be the ideal solution, but with memory as cheap as it is this is becoming less of an issue.
InnoDB has been designed for maximum performance when processing large data volumes.