MySQL: the Pros and Cons of MyISAM Tables

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

Free book: Jump Start HTML5 Basics

Grab a free copy of one our latest ebooks! Packed with hints and tips on HTML5's most powerful new features.

  • Opeyemi Obembe

    The funny thing about table types in MySQL is that u most def will be use to the one you started web development with and may close your eyes at the other options. I, personally, have been in development for a while now and have never considered using any other table type. Well, guess I will have to rethink now.
    And by the way, my dirty workaround to transactions is checking query execution with php’s mysql_affected_rows.
    Nice article. Looking forward to the innoDb part tmrw.

  • filterfish

    I’ve used mysql for about four years (I now avoid it like an STD) and I don’t think there is ever a time where I could recommend it. Maybe FTI but a dedicated FTI (Xapian, Hyper Estraier) is far better, if more work.

    The problem with using MyISAM is that it lulls you into a false sense of security. When you are starting out you don’t know what you don’t know and as you knowledge grows you forget that someone once said it *will* eat your data and you carry on thinking everything’s ok. I mean, it didn’t tell me there was anything wrong. And that’s when it’ll get you and there’s generally nothing you can do about it.

    Let’s face it having multiple table types is a failed experiment; get rid of the myriad table types and concentrate on making one at least not half decent.

  • khuramyz

    Great Article. Keep up the good work craig.

  • http://www.optimalworks.net/ Craig Buckler

    get rid of the myriad table types and concentrate on making one at least not half decent

    To be fair to the MySQL developers, MyISAM foreign keys are planned in v6. Also, some engines such as BDB have been removed. As we will see tomorrow, MyISAM probably isn’t the best default table choice in many cases.

    my dirty workaround to transactions is checking query execution with php’s mysql_affected_rows

    Mmm. That is dirty. Some would say filthy. It’s probably good enough in many cases, but it’s no real substitute for transactions.

    Any number of failures could occur between your update and checking the affected rows (web server crash, MySQL failure, page coding bug, etc).

    Also, handling errors would be tricky. What if you deleted hundreds of records but found that too many had gone? The data has disappeared forever. At least with transactions you can roll back.

  • http://www.joecianflone.com ChestRockwell

    I worked at a startup a few years ago as a front end developer and when our new CTO came in and saw our DB was using MyISAM he went nuts. Scrapped all his plans and made his focus switching everything to innoDB. Had no idea why he was so worked up about it, I didn’t know about the whole corruption issue till today. Now it makes sense, we were dealing with hospital records, that could have been really messy if we lost that info.

  • bob

    MySQl is fast? Ru kidding? Mysql community 64 bit – 4500 batch inserts/sec Oracle Express 32 bit 100K batch inserts/sec same data same machine. same os.

    You are baked.

  • http://www.optimalworks.net/ Craig Buckler

    As mentioned, bulk inserts/updates is not a strong point of MyISAM tables. Selects are another matter – MySQL generally beats most DBs by a significant margin (although every manufacturer will tell you that theirs is the fastest – any DB can be tweaked to work better on one than another). Remember that selects are more frequent in the majority of applications.

    In fact, some large-scale data warehouses have used Oracle or another DB in the backend, but used replicated MySQL servers on the frontend to serve data. That gives you the best of both worlds.

  • Johan Martin

    If you’re building a banking transaction application, should you be using MySQL in the first place?

  • http://www.optimalworks.net/ Craig Buckler

    If you’re building a banking transaction application, should you be using MySQL in the first place?

    There is no reason why you can’t (as long as you choose InnoDB). Perhaps more will adopt open source databases given the current state of the banking sector!

  • http://www.bitsymphony.com Kailash Badu

    Nice write-up Craig,

    One important feature that distinguish MyISAM from InnoDB is that MyISAM tables are generally binary portable. If you need to move a table from one MySQL server to the other, just copy the files from first server to the second server and you are ready to go. In some instances it’s a lot handier than using say mysqldump.

    @bob and nowhere did he say MySQL is faster than Oracle. Moreover, Comparing MySQL with Oracle is besides the point because the two things aren’t on the same wavelength.

  • http://www.optimalworks.net/ Craig Buckler

    I recently copied InnoDB files from one Windows PC to another too. They were placed in a matching folder, but it did work.

    I think there’s also a minor issue that MyISAM tables can only exist in a single file so there may be OS file size limits. But that’s really not a significant problem unless you’ve got a huge DB on FAT or FAT32.

  • http://www.bitsymphony.com Kailash Badu

    @craig

    InnoDB tables only create .frm files in the data directory which holds information on table structure. MyISAM, on the other hand, also saves data file (.myd) and index file(.myi) in the data directory. So I am surprised that you successfully copied InnoDB to another machine.

    What files did you copy?

  • http://www.optimalworks.net/ Craig Buckler

    @Kailash Badu
    The whole MySQL data folder was copied and an identical version of MySQL was used. It worked, although I dumped the structure and data and reinstalled properly after that.

    I wouldn’t really recommend that process, and it was only done following a PC failure.

  • http://www.bitsymphony.com Kailash Badu

    My guess is that only the table structure was copied given that INNODB only saves table structure inside the data directory. Since one could actually see the tables in the second server, it gives the false impression that the database was copied successfully. However, not only the tables are empty (since data was not copied), the tables are broken, unusable in most cases as you cannot really query them.

  • Tintin

    does anyone knows any table that shows the advantages and disavantage of MyISAM and InnoDB? Thanks.

  • http://www.optimalworks.net/ Craig Buckler

    @Tintin
    The main disadvantage of InnoDB is full-text search (MyISAM offers some useful search-engine-like facilities). However, you can get around that by writing your own search routines.

    If all else fails, create a MyISAM table that is purely used for full-text searches and update it only when necessary.