Question

InnoDB: the best storage engine for MySQL?

Answer 1

What is InnoDB?
Why use InnoDB in MySQL?
What are the advantages and disadvantages of combining MySQL and InnoDB?
How are InnoDB and MyISAM different?

---

InnoDB: the best storage engine for MySQL?

If a large website is very slow, it is often because its database technology is not optimized. Order is half the battle - this applies to both the physical and digital worlds. If the data records are constantly growing, a suitable database management system or DBMS is needed . One of these DBMS is MySQL, which allows you to process, store and preserve large amounts of data..

In storing data in MySQL, InnoDB plays an important role. InnoDB is a storage subsystem that has prevailed over other storage engines in recent years, mostly due to its higher performance and reliability . Here we tell you what InnoDB is, what disadvantages it has and how it differs from the MyISAM memory subsystem.

Index

1. What is InnoDB?
2. Why use InnoDB in MySQL?
3. What are the advantages and disadvantages of combining MySQL and InnoDB?
4. How are InnoDB and MyISAM different?

What is InnoDB?

InnoDB has gone from being a storage subsystem to a general purpose storage engine for MySQL. Its great reliability, even at very high performance, has led MySQL to make InnoDB the default storage engine in versions 5.6 and later..

Note

MySQL is an open source database that allows the use of different storage engines. This offers a lot of flexibility to databases for web storage. Besides InnoDB and its official predecessor, MyISAM, there are other engines, such as BerkeleyDB, CSV, NDB or Federated Engine.

As a database management system, MySQL ensures that data can be stored, indexed, and queried in a table . If, for example, a large number of small amounts of data must be read, such as with websites or applications, or if the data needs to be processed securely to carry out transactions, this program offers the right solution. InnoDB, which provides MySQL with the necessary storage engine, behaves like a module that can be flexibly integrated into the software . However, MySQL always determines how the data in a database is stored..

Note

Databases consist of tables that are assigned a specific storage engine, such as InnoDB, at the time of creation.

Most engines store data on data carriers or store it in main memory for quick access. InnoDB allows data transactions to occur in isolation from each other: only when a transaction completes , it is written to the corresponding storage medium. In this way, incomplete accesses to created databases do not occur.

Note

The transactions are data packets consisting of several operations that can not be separated. If an operation cannot be executed, the entire transaction is canceled. To fix it, all previous operations must be undone, not just the wrong one. This process takes more time than a simple operation, but only in this way can you ensure database consistency.

Why use InnoDB in MySQL?

Each InnoDB table organizes the data on the data carrier . This slows down access a bit, but improves security, as each table is optimized with a primary key. Selecting data is relatively faster, but adding and updating it is slower. For this reason, InnoDB is a storage engine for MySQL especially suitable for large databases , in particular, when a lot of data is interlaced.

As already mentioned, the data for each transaction in InnoDB is intertwined. When deleting some data, InnoDB also automatically deletes all referenced data. This makes it much easier for the user to maintain the referential integrity of the database . However, referential integrity must be defined beforehand, because it is not automatically maintained otherwise. In the same way, write access to data records can be blocked.

The InnoDB tabular structure is stored in FRM files , usage data and indexes, in a table space associated with the database that can span one or more files. Referential integrity also applies here: the table space configuration can be spread across different directories, but this must be set at the beginning of the job and cannot be changed . Working with an InnoDB table is in this respect like working with a partition on the hard disk, where, if later changes are made, data can be lost.

Probably the most important example of using InnoDB is mediawiki, the software on which, among others, the Wikipedia website is based.

Note

A proper database system is one of the most important components for managing a web application properly. IONOS web hosting solutions provide you with all the necessary performance and offer you a tailor-made package with web space, work memory and your own domain.

What are the advantages and disadvantages of combining MySQL and InnoDB?

Basically InnoDB is younger and more modern than other MySQL storage engines. It is also more complex and requires a much more powerful database environment . However, as web applications themselves become more complex, operators increasingly rely on the capabilities (transactions, foreign keys) that MySQL can provide thanks to InnoDB: all processes on a web page are based on reading the database content. InnoDB can slow down response time, for example if posts are updated or new articles are created. However, the decisive part, the delivery of new content to the user , is much faster .

InnoDB's drawbacks are outweighed by its advantages, at least on common modern websites. In the case of online stores , however, their limitations are more obvious: in a reservation system, for example, there are many write accesses, so the read-only key system is not particularly useful. In this context, it is recommended to use tables that better handle write access, such as MyISAM.

The most important aspect of using MySQL with InnoDB is that a mistake when making changes to the databases can make the entire website unusable. Therefore, the database must be backed up before every change. For these cases, InnoDB uses a transaction log that allows automatic reset in case of failure.

How are InnoDB and MyISAM different?

Since its version 5, MySQL is compatible with more than ten different storage engines. The best known is MyISAM. This engine stores the data for each table in two files, one for the file and one for the index. ISAM stands for Indexed Sequential Access Method, so it does not allow multiple users or applications to write to the same table at the same time. The sequential storage only allows write access by one application at a time. However, it allows simultaneous reading by multiple users. If many users must read several small data packets at the same time, MyISAM is the best choice.

InnoDB complies with the ACID standard ( atomicity, consistency, isolation, durability ; in Spanish, atomicity, consistency, isolation, durability). All transactions are isolated from each other , but the number of applications that can write data to a table simultaneously is unlimited. InnoDB is the ideal candidate for data queries ( SELECT function ), but MyISAM is somewhat faster for write access to databases ( INSERT and UPDATE functions ). However, the relative slowness of InnoDB is more than offset by the transaction log. MyISAM does not offer automatic reset on errors, so a backup should always be created before making changes.

In summary

The InnoDB storage engine is not always the best replacement for its predecessor, MyISAM. As projects or databases grow in complexity, InnoDB becomes the appropriate solution for MySQL. For smaller databases or applications, MyISAM is much more powerful. If used correctly, there is no question about which of the storage engines to use. If necessary, both modules can be used alongside each other in the same database.

---