MySQL: the Pros and Cons of InnoDB Tables

MySQL InnoDB Following on from our look at MyISAM tables, today we examine InnoDB tables.

InnoDB is less well known than the default MyISAM table type. However, there are several reasons why it could be better choice for your web application.

1. Data integrity and foreign key constraints
Foreign keys establish a relationship between columns in one table and those in another. For example, you might create a library application where books can be loaned to members. A foreign key constraint would ensure that a member existed before a book could be checked-out. Similarly, removing a user would not be possible until all their books were returned.

For further information about foreign keys and automating your database, refer to How to Use MySQL Foreign Keys for Quicker Database Development.

2. Transactions
InnoDB tables support transactions. A transaction allows multiple SQL commands to be treated as a single and reliable unit.

Consider a banking application where you are transferring money from one account to another. The transaction would only be committed if both accounts were altered successfully. If anything failed, the database would be rolled-back to a previous state.

In addition, InnoDB tables recover well from crashes. MySQL will analyze the log files to ensure the data is accurate so there is no need to repair tables.

3. Row-level locking
InnoDB uses row-level rather than table-level locking. If a row is being inserted, updated or deleted, only changes to the same row are held up until that request has completed. Tables that receive more updates than selects may be faster with InnoDB.

The disadvantages of InnoDB tables

There are a number of situations when InnoDB may not be suitable.

1. Increased complexity
Creating an InnoDB table is no more complex than MyISAM, e.g.


CREATE TABLE employee ( 
id smallint(5) unsigned NOT NULL, 
firstname varchar(30), 
lastname varchar(30), 
PRIMARY KEY (id)
) ENGINE=InnoDB;

However, designing that database with foreign key relationships does require more effort. Database novices will find MyISAM easier because it has fewer features.

2. No full-text search
InnoDB tables do not support full-text searches; it is not easy to match one or more keywords against multiple columns.

3. Slower performance
If your application is primarily selecting data and performance is a priority, MyISAM tables will normally be faster and use fewer system resources.

Should you use InnoDB?

InnoDB is the best option if you need to create a reliable data-driven web application. In many ways, InnoDB is a better default choice than MyISAM:

unless you have a significantly large or heavily-used system, the speed differences are likely to be negligible
full-text searches can be implemented in other ways, e.g. more complex SQL or server-side search algorithms.

InnoDB is certainly the best choice for online shops, financial applications or any project where data integrity is essential. Defining tables is more complex, but your application will be more robust and may require less server-side code.

Note that you can mix table types within the same database. In practice, it may cause less developer confusion if you stick with a single table type.

Frequently Asked Questions (FAQs) about MySQL InnoDB Tables

What are the key differences between InnoDB and MyISAM in MySQL?

InnoDB and MyISAM are two different storage engines in MySQL. InnoDB supports transactions, row-level locking, and foreign keys, making it a good choice for large database systems where data integrity and security are crucial. On the other hand, MyISAM is simpler and faster, but it lacks some of the advanced features of InnoDB. It’s best suited for read-heavy applications or small systems where speed is more important than advanced features.

Why should I choose InnoDB over other storage engines?

InnoDB is a robust and feature-rich storage engine that provides several advantages over other engines. It supports ACID-compliant transactions, which ensures data integrity even in case of a system crash or power failure. It also supports row-level locking, which improves performance in multi-user environments. Moreover, InnoDB’s support for foreign keys enables enforcing referential integrity, which is crucial for relational databases.

What are the potential drawbacks of using InnoDB?

While InnoDB is powerful, it may not be the best choice for every situation. It can be more complex to set up and manage than simpler engines like MyISAM. It also uses more system resources, which can be a concern in resource-constrained environments. Additionally, while InnoDB is generally faster than MyISAM for write-heavy workloads, it can be slower for read-heavy workloads.

How can I convert a MyISAM table to an InnoDB table?

You can convert a MyISAM table to an InnoDB table using the ALTER TABLE command in MySQL. Here’s an example:
ALTER TABLE table_name ENGINE=InnoDB;
Please replace “table_name” with the name of your table. Remember to backup your data before making such changes.

How does InnoDB handle transactions?

InnoDB uses a multi-version concurrency control (MVCC) system to handle transactions. This allows multiple transactions to access the same data without interfering with each other. Each transaction works with a “snapshot” of the data, which ensures data consistency even when other transactions are modifying the same data concurrently.

What is the InnoDB buffer pool?

The InnoDB buffer pool is a memory area that InnoDB uses to cache data and indexes of its tables. This reduces the need for disk I/O and hence improves performance. The size of the buffer pool is configurable and can significantly impact the performance of InnoDB.

How does InnoDB ensure data integrity?

InnoDB ensures data integrity through a combination of features. It supports ACID-compliant transactions, which ensure that all parts of a transaction are completed successfully or none at all. It also supports foreign keys and referential integrity constraints, which prevent inconsistent data from being inserted into the database.

Can I use both InnoDB and MyISAM in the same database?

Yes, MySQL allows you to use different storage engines for different tables within the same database. This means you can choose the most appropriate engine for each table based on its specific requirements.

How does InnoDB handle indexing?

InnoDB uses a data structure called a B+ tree for indexing. Each table has a primary key index, and can also have multiple secondary indexes. Indexes in InnoDB are physically stored in the same way as table data, which is different from some other storage engines where indexes are stored separately.

What is the InnoDB log file and how does it work?

The InnoDB log file is a part of the InnoDB storage engine that records changes made to the data. It’s used to ensure data integrity in case of a crash or power failure. When a transaction is committed, the changes are first written to the log file. This allows InnoDB to recover the database to a consistent state by replaying the changes from the log file if a crash occurs.