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, usinga 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?

What is InnoDB?

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.

Note

MySQL is open-source and can be used with different storage engines, so it’s a very flexible solution for web hosting. In addition to InnoDB and its official predecessor MyISAM, there are also other engines available, such as BerkeleyDB, CSV, NDB, and Federated Engine.

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.

Note

Databases consist of multiple tables, each of which is assigned to a specific storage engine like InnoDB when they are set up.

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.

Note

Transactions are data packets consisting of a set of related operations that are treated as a whole. If any one operation cannot be completed, the entire transaction fails. To resolve the issue, the failed operation has to be corrected and all of the previous operations have to be rolled back. This takes more time than a single operation but is the only way of ensuring that a database remains consistent.

Why use InnoDB in MySQL?

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 integrityof 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.

Note

Having a suitable database system is one of the keys to ensuring your web application runs properly. The IONOS web hosting solutions provide powerful database capabilities in a customised package with a webspace, memory, and your own domain.

What are the advantages and disadvantages of using InnoDB with MySQL?

InnoDB is newer and more modern than other MySQL storage engines. However, it’s also morecomplex 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.

What’s the difference between InnoDB and MyISAM?

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.

Summary

The InnoDB storage engine excels if you’re dealing with especially large or complex projects or data sets, but it’s not always a better choice than its predecessor, MyISAM. For smaller applications and databases, MyISAM offers much higher performance. And in fact, you don’t necessarily have to choose between them. If necessary, both storage engines can be used side by side within the same database.


Wait! We’ve got something for you!
Get your .co.uk domain for just £1/1st year.

Enter the web address of your choice in the search bar to check its availability.
12 month for £1/1st year
then £10/year