What is InnoDB and what are the differences compared to other engines?

The database systems MySQL and MariaDB represent the current most widely used standard for databases on websites. However, they only define the way in which the system stores data - as relational in tables created with fixed criteria. For direct interaction with the databases and their manipulation, MySQL and MariaDB use an external storage subsystem (Engine) that acts as the interface between the database management system (DBMS) and the information. For both systems, this task has been handled by the storage engine InnoDB for several years.

What is the role of an engine like InnoDB?

The main task of InnoDB and other storage subsystems is to create an additional intermediate layer between the software responsible for management and the stored information. This layer serves to administer accesses, lock records, and coordinate parallel queries or write operations from different sources. The DBMS in the form of MySQL or MariaDB merely provides the framework, defining the data structure and command syntax. It then delegates tasks, including the data, to InnoDB or another engine for direct data handling.

What's Behind the Distinction Between MySQL and InnoDB?

By using a fixed system with different storage engines, MySQL and MariaDB possess a flexibility that could not be achieved through a fixed integration. Abstracted, InnoDB and other engines take on the role of plugins, allowing a modular structure in core functions of the database server. This separation also facilitates maintenance and optimisation of the source code, as the integration of storage subsystems from external providers allows a concentration of all resources on the development of MySQL or MariaDB. InnoDB, for example, originally comes from the Finnish company Innobase Oy, which the software giant fully integrated into its own structure after acquiring it in 2005 over the following years.

What Are the Advantages of InnoDB Over Other Engines?

Up to version 5.0, MySQL used MyISAM (My Indexed Sequential Access Method) as a storage subsystem, but abandoned it in favour of InnoDB as the standard. Most content management systems (CMS) and shop software use InnoDB as the standard, while many plugins still rely on MyISAM. This difference in preference is primarily based on the specific characteristics of the two engines. Some of the features of InnoDB include:

• Locking only a row with a record instead of a table
• Secure access through transactions
• Transactions can be aborted and rolled back before completion
• Integrated options for creating and restoring backups
• Automatic write lock for other transactions during write access
• Database recovery after a crash
• Fast SELECT queries (reading data)
• Support for foreign keys for transaction security and integrity

In contrast, MyISAM is characterised by the following properties:

• Integrated search for full text
• No support for referential integrity
• Access to databases in individual steps instead of bundled transactions
• No control of databases for consistency and completeness of access
• Fast INSERT and UPDATE accesses (reading data)
• Efficient use of memory in RAM and on the hard drive

The reason why InnoDB requires significantly longer access times when writing data is due to the strict control of databases for their consistency. In contrast to MyISAM, InnoDB monitors every access until it is successfully written to memory or a file. MyISAM, on the other hand, simply issues an instruction and leaves the control of changes solely to the operating system.

How can databases be assigned to a specific engine like InnoDB?

Working with databases is rarely done manually - if such interventions are necessary, comfortable web interfaces such as phpMyAdmin are recommended for this purpose. The standard practice is to use the PHP extension MySQLi (MySQL Improved) - since version 7.0, the programming language no longer supports its previous version MySQL. The simplest way is to specify the storage subsystem when creating databases by using the option ENGINE =. It is also possible to convert later by executing the following command in MySQL, through MySQLi, or in phpMyAdmin:

ALTER TABLE name ENGINE=myisam/innodb;

However, conversion carries the risk of data loss, and a mixed operation between InnoDB and other engines is only recommended to a limited extent.

Alternatives to InnoDB include Redis, InfluxDB, and MySQLi.

Photo: Gerd Altmann on Pixabay