MySQL: the Pros and Cons of InnoDB Tables

Unlike the majority of database systems, MySQL offers a number of table types. Choosing the correct type can be critical for your web application, but few developers realise the implications until it is too late.


MyISAM - Advantages

1. Simplicity
2. Speed
3. Full-text searching

MyISAM - Dis-Advantages

1. Poor data integrity
2. Poor crash recovery
3. Table locking

Should you use MyISAM?

MyISAM is an ideal choice if you are new to MySQL, your web application is simple, has to be fast, or use full-text searches. However, it should not be used when data integrity is a priority. Possible applications could include content management systems, online bookmarking tools, RSS readers, or simple search engines.

InnoDB Advantages

1. Data integrity and foreign key constraints
2. Transactions
3. Row-level locking

InnoDB Dis-Advantages

1. Increased complexity
2. No full-text search
3. Slower performance

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.

READ COMPLETE ARTICLE @ :: Part 1 & Part 2