MySQL: the Pros and Cons of InnoDB Tables

MySQL InnoDBFollowing 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:

  1. unless you have a significantly large or heavily-used system, the speed differences are likely to be negligible
  2. 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.

See also:

Will you consider InnoDB tables for your next application?

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • Rory

    Like me, many other people are prbably saying right now that they should change their table structure from MyISAM to InnoDB. Perhaps you can tell us how best to make this change with an existing database.

  • praetor

    Can’t wait for the Postgres articles ;)

  • http://www.optimalworks.net/ Craig Buckler

    @Rory
    Use the best table engine for the job in hand, although I do consider InnoDB to be a better choice for the majority of applications.

    You can change an existing table to InnoDB using:

    alter table mytable Engine=InnoDB;

    Please don’t do that on a live system! Test it thoroughly first.

  • Stefan

    I’m not so sure whether MyISAM is faster than InnoDB even for a read only benchmark. Twice I had the opportunity to run some read only benchmarks against MySQL and in both cases InnoDB was to my surprise faster than MyISAM (within a range of 10-40%). Are there any benchmarks available that show a performance advantage for MyISAM?

    (Apart from that I really think that the ACID properties aren’t a feature, but a requirement for a database.)

  • http://www.optimalworks.net/ Craig Buckler

    @Stefan
    That’s interesting. I did find a few resources that indicate InnoDB could be faster in tests, although it didn’t scale as well as MyISAM. Every DB has different requirements, so it’s a little difficult to prove what’s best.

    I suspect MyISAM is slower or offers negligible speed benefits in all but the largest of read-only tables.

  • williamb

    Valuable information, short and to the point. I have been working with MySQL for quite a few years now and honestly just never bothered to learn the difference between InnoDB and MyISAM because MyISAM just ‘worked’. I definitely could have used this info about two months ago for a project I was working on but it will be useful from here on out. Thanks again…

  • Chris

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

    This statement isn’t always true, and therefore I think it should have been omitted. Performance is dependent on what your application does and the kinds of queries it handles. A novice will see the subheading “Slower performance”, and might think InnoDB is slower, but again that’s not true and is very misleading.

    InnoDB’s use of row level locking means it can be a lot faster than MyISAM. Way faster in certain cases.

    Certainly, since MyISAM is the default engine and its straightforward, it is good for beginners. But beginners shouldn’t be told InnoDB = “Slower Performance”.

    I’d recommend the book “High Performance MySQL” (2nd edition) to anyone thinking of building websites are more than just a hobby site.

  • Brad Montgomery

    @Rory, @Craig Buckler

    You can definitely switch from MyISAM to InnoDB using “alter table”, but to really reap the benefits of InnoDB you’d probably need to re-design your tables (i.e. explicitly specifying any foreign key constraints)

    You might be able to save yourself some time re-entering data by using mysqldump to save your existing data, and just keep INSERT statements (which you may have to alter).

    Of course, this all depends on your tables’ structure. :)

  • http://www.vancelucas.com Czaries

    I think the InnoDB engine should be the default. I really don’t view them as any more complicated than MyISAM, and the benefits they provide far outweigh any perceived costs. In my mind, the only justification for ever using MyISAM on ANY project (large or small) is for search index tables due to the FULLTEXT search capabilities. Data integrity should be the top priority for databases, big or small.

  • Jose G

    For high traffic web sites, having an INNO DB Master with MyISAM Slaves for reading (selects) is a good option.

  • JJ

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

    It is not database novices who will find MyISAM easier, but database ignorants.
    Just because clicking here and there in phpMyAdmin creates a database you don’t become a database designer. If you don’t know what a primary key is, or what foreign keys and referential integrity are, then you don’t know anything about database design.