Tuesday, March 22, 2011

MySQL: the Pros and Cons of MyISAM InnoDB Tables

SkyHi @ Tuesday, March 22, 2011
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. In this article we examine MyISAM tables.MyISAM is the default MySQL table type. If you have never knowingly specified a type, your table will use the MyISAM engine. There are several reasons why that could be an advantage.

1. Simplicity
MyISAM tables are simple. If you are new to MySQL or databases in general, I would recommend you start with MyISAM tables.
In the following example, we will create a new database and table from the mysql> command prompt. (You do have MySQL installed, right? If not, take a look at How to Install MySQL.)

USE mysqltest;
CREATE TABLE mysqltest.articles (

This creates a new database, mysqltest, and a new table named articles with the columns id (an automatically generated number), title and body. Determining the best column data type is about as complex as it gets.

2. Speed
MySQL is fast. Really fast. Under normal conditions, benchmarks show that MyISAM tables beat every other general-purpose database and uses fewer system resources.

3. Full-text searching
Consider our table above. What if we need to search for keywords in both the title and the body? An easy solution is to add a fulltext index:

ALTER TABLE mysqltest.articles ADD FULLTEXT alltext (title, body);
We can now find all articles that feature the words “database” and/or “article” using:

SELECT * FROM mysqltest.articles
WHERE MATCH(title, body) AGAINST ('database article');
We can even order articles by the most relevant first to create a simple search engine:

SELECT *, MATCH(title, body) AGAINST ('database article') AS rel
FROM mysqltest.articles

The disadvantages of MyISAM tables

There are several situations when MyISAM may not be suitable.
1. Poor data integrity
MyISAM tables do not support transactions or foreign key constraints.
Consider a banking application where you are transferring money. This would typically involve two SQL UPDATE statements; a debit from one account and a credit to another. If MySQL or the server failed at the wrong point, you could end up with money in both accounts or neither.

2. Poor crash recovery
MySQL is reliable, but MyISAM tables can become corrupt following a crash. The problem can usually be fixed using REPAIR TABLE but it is an additional administrative overhead.

3. Table locking
MyISAM uses table-level locking. When a row is inserted or updated, all other changes to that table are held up until that request has been completed.
It is not always easy to prove whether this will cause problems for your web application but, in general, if you are performing more inserts/updates than selects, MyISAM may not be the best table type to choose.

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.
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:

MySQL: the Pros and Cons of InnoDB Tables

 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),
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.