MySQL: the Pros and Cons of MyISAM Tables

Share this article

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

Frequently Asked Questions about MySQL MyISAM Tables

What are the key differences between MyISAM and InnoDB storage engines?

MyISAM and InnoDB are two different storage engines used in MySQL. MyISAM is simpler and has less overhead, making it faster in many situations. However, it lacks transaction capabilities and isn’t as reliable as InnoDB. On the other hand, InnoDB supports transactions, row-level locking, and foreign keys, making it more suitable for complex, high-volume applications.

Why would I choose MyISAM over InnoDB?

MyISAM might be a better choice for read-heavy applications, as it’s generally faster for read operations. It also supports full-text indexing, which can be useful for certain types of searches. However, it’s important to consider the trade-offs, as MyISAM doesn’t support transactions or foreign keys.

What are the limitations of MyISAM?

MyISAM has several limitations. It doesn’t support transactions, which can lead to data inconsistency in case of a crash. It also doesn’t support foreign keys or row-level locking, which can limit its performance in multi-user environments. Additionally, MyISAM tables can become corrupted more easily than InnoDB tables.

How can I convert a MyISAM table to InnoDB?

You can convert a MyISAM table to InnoDB using the ALTER TABLE command in MySQL. The syntax is: ALTER TABLE table_name ENGINE=InnoDB; However, you should always backup your data before performing such operations.

What is the impact of MyISAM’s table-level locking?

MyISAM’s table-level locking can limit its performance in multi-user environments. When a user is writing to a MyISAM table, no other user can write to it at the same time. This can lead to a bottleneck if many users are trying to write to the same table simultaneously.

How does MyISAM handle crashes?

MyISAM has a crash recovery mechanism that attempts to repair corrupted tables automatically. However, this mechanism isn’t always successful, and data loss can occur. This is one of the reasons why MyISAM isn’t recommended for critical applications.

Can I use transactions with MyISAM?

No, MyISAM doesn’t support transactions. If you need to use transactions, you should consider using InnoDB or another storage engine that supports them.

How does MyISAM handle indexing?

MyISAM supports both primary and secondary indexes. It also supports full-text indexing, which can be useful for certain types of searches. However, MyISAM’s indexing isn’t as sophisticated as InnoDB’s, and it can be slower for write-heavy workloads.

What is the maximum size of a MyISAM table?

The maximum size of a MyISAM table is determined by the operating system’s file size limit. On most modern systems, this limit is very high, so it’s unlikely to be a practical constraint.

How can I optimize a MyISAM table?

You can optimize a MyISAM table using the OPTIMIZE TABLE command in MySQL. This command reorganizes the table and rebuilds its indexes, which can improve performance. However, it’s important to note that the table will be locked during the optimization process.

Craig BucklerCraig Buckler
View Author

Craig is a freelance UK web consultant who built his first page for IE2.0 in 1995. Since that time he's been advocating standards, accessibility, and best-practice HTML5 techniques. He's created enterprise specifications, websites and online applications for companies and organisations including the UK Parliament, the European Parliament, the Department of Energy & Climate Change, Microsoft, and more. He's written more than 1,000 articles for SitePoint and you can find him @craigbuckler.

Share this article
Read Next
Get the freshest news and resources for developers, designers and digital creators in your inbox each week
Loading form