InnoDB – the best storage engine for MySQL?

When large websites are slow to load, poorly optimised databases are often to blame. Organisation is half the battle, and that counts for all things digital too. As databases grow, using a suitable Database Management System (DBMS for short) like MySQL becomes essential. A DBMS allows you to easily process, save, and permanently store large amounts of data.

Within MySQL, InnoDB plays an important role in data storage. It is a storage subsystem and in recent years it has become one of the most popular choices thanks to its high levels of performance and reliability compared to other storage engines. Let’s take a closer look at InnoDB. Does it have any disadvantages? How is it different from the MyISAM storage engine?

InnoDB has evolved from being a storage subsystem to a general-purpose storage engine for MySQL. Thanks to its combination of high performance and high reliability, it was made the default storage engine from Version 5.6 onwards.

MySQL is what’s known as a Database Management System (DBMS). A DBMS is a tool that allows you to store, index, and retrieve data in a table. MySQL can handle large volumes of reads and writes (e.g. for websites and apps), and situations where transaction-safe data processing capabilities are required. InnoDB provides MySQL with the storage engine needed to fulfil these functions, which operates as a module that can be easily integrated into the software. However, it is MySQL that determines how data is saved to a database.

Most storage engines either store the data on a disk or keep it in the main memory for quick access. InnoDB implements isolated transactions which means data is only written to the corresponding storage medium when a specific transaction is complete. This ensures incomplete changes are not stored to the database.

Each InnoDB table arranges the data on the disk in order to optimise queries with a primary key. This makes access a little slower but also much more secure. Data selection is relatively faster, but inserting and updating take longer. As such, InnoDB is best suited to large databases and in particular those that contain lots of relational data.

As mentioned above, with InnoDB, all the data in a transaction is treated as a single unit. Consequently, if some of the data is deleted, InnoDB will automatically delete all of the referenced data too. This makes it much easier for users to maintain the referential integrity of the database. However, this referential integrity can only be maintained if it is defined in advance. The same principle can be used to lock write access to data records.

The InnoDB table structure is saved in FRM files, user data, and indexes in a tablespace that is linked to the database. The tablespace can contain one or more files. Referential integrity applies here too. The tablespace can be configured across several directories, but this must be set up at the beginning and can’t be modified at a later date. It might be helpful to think of working with InnoDB tables as partitioning a hard drive – changing things later on can lead to data loss.

One of the best-known uses of InnoDB is MediaWiki, the software that powers Wikipedia and other sites.

InnoDB is newer and more modern than other MySQL storage engines. However, it’s also more complex and requires a much more powerful database environment. Despite this, website operators are increasingly relying on the capabilities (transactions, foreign keys) offered by the InnoDB/MySQL combination because web applications themselves are becoming ever more complex. All the processes on a website rely on reading database content. InnoDB can slow down response times, such as when articles are updated or added. On the other hand, the critical part – delivering new content to site visitors – is much faster.

When used for a standard modern website, the pros and cons of InnoDB balance each other out. However, for e-commerce sites, the limits of InnoDB quickly become apparent. For example, online booking systems are very write-heavy. In this case, the write lock mechanism of InnoDB means this storage engine isn’t the best choice. Instead, you should use tables that allow better handling of writes, such as MyISAM.

By far the most important point to note when using MySQL with InnoDB is that incorrect changes within the database can make an entire website unusable. Before any modification is made, it’s therefore essential that the existing database is backed up. That’s why InnoDB uses a transaction protocol that supports automatic recovery in the event of an error.

Since version 5, MySQL has supported more than ten different storage engines. The best-known of these is MyISAM. MyISAM stores the data from the individual tables in two files: a data file and an index file. ISAM stands for Indexed Sequential Access Method. In other words, records are stored sequentially and only one user or application has write permission at any one time – although several users can still read a table at the same time. If lots of small data packets need to be read quickly by many users at the same time, MyISAM can’t be beaten.

InnoDB is ACID-compliant (ACID: Atomicity, Consistency, Isolation and Durability). All of the transactions run in isolation, but any number of applications can write data to a table at the same time. In terms of data queries (SELECT), InnoDB is the clear winner, but when it comes to database writes (INSERT and UPDATE), MyISAM is somewhat faster. However, the lower speed of InnoDB is more than compensated for by its transaction protocol. With MyISAM, there is no automatic crash recovery mechanism, so it’s important to create a backup before making any changes.