MySQL Table Options: MyISAM, InnoDB
March 31, 2009
Database design is a topic that rarely seems to crop up on web development forums and web sites. Topics usually fall towards software optimization, language wars or endless debate over web frameworks. However, proper database design is essential in building high performance, efficient and secure web applications and is often the source of most performance bottlenecks.
Before spending massive amounts of time code tuning, have a look through your database tier first. Tuning your database correctly for your application most often resolves performance issues. MySQL, the defacto database of most web hosts, is a powerful tool that can be easily tweaked and configured around your application. Getting MySQL tuned to the right settings can mean the difference between lightning-fast and turtle-like response times.
While many methodologies, tools, configuration settings, techniques, etc exist for properly designing your database, this article will focus primarily on MySQL table types. Choosing the right table type for your situation is the first step towards building high performance applications.
Table Types
Within MySQL, there are a number of different table types; MyISAM, InnoDB, Archive, Merge, Memory and more, each one with its own distinct purpose. For example, if you have extremely large data sets that rarely get accessed, Archive may be the type for you. Fortunately in most settings, MyISAM and InnoDB are the only two types really needed to build a robust database tier - so we won't comment on the other types in this article.
What are MyISAM and InnoDB and when should you use them?
MyISAM
MyISAM is the child of the original MySQL ISAM engine and is frequently used as the default table type when creating new tables within MySQL. In a broad sense, MyISAM is geared towards speed and indexing, plain and simple. MyISAM offers support for indexing BLOB and TEXT columns, essentially allowing you to build full text searches within your application. Unfortunately, all that speed comes at a price and that price is data integrity.
InnoDB
InnoDB provides the other side of the coin and offers the full data integrity and transactional support needed that MyISAM lacks. InnoDB is MySQL's transaction-safe table type and excels at integrity guarantees, large data storage and of course transaction support. However, just as MyISAM has trade-offs so does InnoDB. The integrity provided by InnoDB comes with price and those are speed and resources (InnoDB requires more disk space than MyISAM).
...when your transactions and data are critical, such as in e-commerce applications, utilizing InnoDB is generally the way to go.
So now that we have a broad understanding of the differences between the two table types, you may wonder in which circumstance InnoDB should be used over MyISAM or vice versa.
While we cannot definitively say when to use table type x, y or z for any given situation (you will need to analyze your situation as needed), we can say that when your transactions and data are critical, such as in e-commerce applications, utilizing InnoDB is generally the way to go. InnoDB provides the support and structure needed to handle large amounts of data and perform multiple data operations in one unit. However, InnoDB would be a poor choice in situations where you need quick access, such as when utilizing "look-up tables" or static tables containing small amounts of data. In these instances, you will be much better off with the speed of MyISAM.
Determining the correct table types to use is the first step towards creating a fast, sound and secure MySQL database. When your resources are at a premium (in terms of access, amount), such as in shared hosting environments, correctly configuring MySQL is especially critical. In situations such as these, squeezing every last bit out of your database goes a long way and selecting optimal table types will help get you started.
Written by: The Sevenforty Team
Further Reading - Looking for further reading on MySQL? Looking to tune your MySQL database for maximum performance? Check out MySQL Database Design and Tuning (Developer's Library) for one of the best - and most professional - resources we've come across.
