MySQL: the Pros and Cons of MyISAM 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. 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.)

CREATE DATABASE mysqltest;
USE mysqltest;

CREATE TABLE mysqltest.articles (
id int UNSIGNED NOT NULL AUTO_INCREMENT,
title TEXT NOT NULL,
body MEDIUMTEXT,
PRIMARY KEY (id))
ENGINE = MyISAM;

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
ORDER BY rel DESC;

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
• How to Install Apache
• How to Install PHP
• How to Install MySQL
• How to Use MySQL Foreign Keys for Quicker Database Development

Frequently Asked Questions about MySQL MyISAM Tables