MyISAM vs InnoDB, choose your MySQL storage engine

 

The storage engine (storage-engine) is responsible for storing, managing and retrieving information from a table. The best known engines are MyISAM and InnoDB. Choosing one or the other will depend a lot on the scenario where it is applied, but Arsys wants to help us get to know these well-known storage engines better. The choice is intended to achieve the best quality ratio in accordance with our application. If we need transactions, foreign keys and locks, we will have to choose InnoDB. On the contrary, we will choose MyISAM in those cases in which SELECT queries to the database predominate.

InnoDB was designed for maximum performance when processing large volumes of data. Probably no other on-disk relational database engine matches its CPU efficiency.

Despite being fully integrated with the MySQL server, the InnoDB storage engine maintains its own buffer pool to cache data and indexes in main memory. InnoDB stores its tables and indexes in a table space, which can consist of several files (or disk partitions). This differs from, for example, the MyISAM engine, where each table is stored using separate files. InnoDB tables can be of any size, even on operating systems where file size is limited to 2GB.

InnoDB provides MySQL with a transactional (ACID-compliant) storage engine with commit, rollback, and failover capabilities. InnoDB performs row-level locking and also provides Oracle-style lock-free consistent reading functions in SELECT statements. These features increase performance and the ability to manage multiple simultaneous users. A scaled lock is not needed in InnoDB because row-level locks take up very little space. InnoDB also supports FOREIGN KEY constraints. In SQL queries, even within the same query, InnoDB type tables can be freely included with tables of other types.