InnoDB – the best storage engine for MySQL?

When large websites are slow to load, poorly optimized databases are often to blame. Or­ga­ni­za­tion is half the battle, and that counts for all things digital too. As databases grow, using a suitable Database Man­age­ment System (DBMS for short) like MySQL becomes essential. A DBMS allows you to easily process, save, and per­ma­nent­ly 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 per­for­mance and re­li­a­bil­i­ty compared to other storage engines. Let’s take a closer look at InnoDB. Does it have any dis­ad­van­tages? 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 com­bi­na­tion of high per­for­mance and high re­li­a­bil­i­ty, it was made the default storage engine from Version 5.6 onwards.

MySQL is what’s known as a Database Man­age­ment 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 sit­u­a­tions where trans­ac­tion-safe data pro­cess­ing ca­pa­bil­i­ties are required. InnoDB provides MySQL with the storage engine needed to fulfil these functions, which operates as a module that can be easily in­te­grat­ed into the software. However, it is MySQL that de­ter­mines 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 im­ple­ments isolated trans­ac­tions which means data is only written to the cor­re­spond­ing storage medium when a specific trans­ac­tion is complete. This ensures in­com­plete changes are not stored to the database.

Each InnoDB table arranges the data on the disk in order to optimize queries with a primary key. This makes access a little slower but also much more secure. Data selection is rel­a­tive­ly faster, but inserting and updating take longer. As such, InnoDB is best suited to large databases and in par­tic­u­lar those that contain lots of re­la­tion­al data.

As mentioned above, with InnoDB, all the data in a trans­ac­tion is treated as a single unit. Con­se­quent­ly, if some of the data is deleted, InnoDB will au­to­mat­i­cal­ly delete all of the ref­er­enced data too. This makes it much easier for users to maintain the ref­er­en­tial integrity of the database. However, this ref­er­en­tial integrity can only be main­tained 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 ta­ble­space that is linked to the database. The ta­ble­space can contain one or more files. Ref­er­en­tial integrity applies here too. The ta­ble­space can be con­fig­ured across several di­rec­to­ries, 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 par­ti­tion­ing 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 en­vi­ron­ment. Despite this, website operators are in­creas­ing­ly relying on the ca­pa­bil­i­ties (trans­ac­tions, foreign keys) offered by the InnoDB/MySQL com­bi­na­tion because web ap­pli­ca­tions them­selves 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 – de­liv­er­ing 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 mod­i­fi­ca­tion is made, it’s therefore essential that the existing database is backed up. That’s why InnoDB uses a trans­ac­tion 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 in­di­vid­ual tables in two files: a data file and an index file. ISAM stands for Indexed Sequential Access Method. In other words, records are stored se­quen­tial­ly and only one user or ap­pli­ca­tion has write per­mis­sion 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, Con­sis­ten­cy, Isolation and Durability). All of the trans­ac­tions run in isolation, but any number of ap­pli­ca­tions 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 com­pen­sat­ed for by its trans­ac­tion protocol. With MyISAM, there is no automatic crash recovery mechanism, so it’s important to create a backup before making any changes.