MySQL: the Pros and Cons of InnoDB Tables

March 26, 2009

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.