When MyISAM is better than InnoDB?

Question

Sometimes I got asked on some interviews: what benefits does InnoDB have against MyISAM and when MyISAM is better than InnoDB? It's all clear about the first part of question: InnoDB is transaction compliant, row-level blocking instead of table-level blocking, foreign key support and some others, these points just came to mind immidiately.

But when MyISAM is really better than InnoDB?

Jemappes

Answer 1

MyISAM is better than InnoDB when you don't need those advanced features and storage speed is more important than other concerns. MyISAM also allows full-text searches to be performed inside the database engine itself, instead of needing to query results and then search them as an array or whatever in your application.

InnoDB is a reasonable choice if you need to store data with a high degree of fidelity with complicated interactions and relationships. MyISAM is a reasonable choice if you need to save or load a large number of records in a small amount of time.

I wouldn't recommend using MyISAM for data that matters. It's great for logging or comments fields or anything where you don't particularly care if a record vanishes into the twisting nether. InnoDB is good for when you care about your data, don't need fast searches and have to use MySQL.

It's also worth mentioning that InnoDB supports row-level locking, while MyISAM only supports table-level locking - which is to say that for many common situations, InnoDB can be dramatically faster due to more queries executing in parallel.

The bottom line: Use InnoDB unless you absolutely have to use MyISAM. Alternatively, develop against PostgreSQL and get the best of both.

Cortes

It still depends. If you need ACID guarantees, then MyIASM is not for you. Even if you need speed. – Alcazar

Actually just amended my post to that effect. As I alluded to in the edited text, I can't really countenance MySQL's use in anything terribly important. – Cortes

Let's get this party started lxadm.com/… – Backsight

For those who are wondering like I was, ACID stands for Atomicity, Consistency, Isolation, and Durability. It's a set of properties of database transactions intended to guarantee data validity. – Balough

Answer 2

MyISAM doesn't support transactions (and the other things mentioned) so it can work faster. MyISAM is a way to achieve higher performance in those situations when you do not need these features.

Daciadacie

"These features" being everything that makes an RDBMS an RDBMS: ACID (It's a bit of a hyperbole, but still...) – Alcazar

Believe me, when you need that extra performance, dropping those features of RDBMS-es is often the way to go. Check the current popular NoSQL solutions and rething what you though about the necessity of ACID. – Daciadacie

the issue here is that MySQL drops them in ways that harm its ability to do the job it's advertised to do. MyISAM is infamous for eating data for esoteric or unknown reasons, and the popular NoSQL solutions are managing to drop relational logic without murdering functionality. – Cortes

I have different opinion but I'm not going to argue with you about MySQL's ability to do the job.. but would like to note that life is unfortunately very complex. Although some NoSQL solutions are more fit for specific tasks companies still use MySQL simply because of its ubiquity. It's easy to hire developers who are familiar with it and takes time to teach them a whole new database (or more databases, for specific tasks). I'm not saying this is good or more economic I'm just telling you what I've experienced in my career. So if they need peformance, they swith/replicate to MyISAM. – Daciadacie

Answer 3

MyISAM supports full text, as mentioned, but also supports the MERGE table type. This is handy when you have a large table and would like to "swap" out/archive parts of it periodically. Think about a logging or report data that you want to keep the last quarter and/or year. MyISAM handles large amounts of data like this better, when you are mainly inserting and rarely updating or deleting.

InnoDB performance drops pretty quickly and dramatically once you can't fit the indexes in memory. If your primary key is not going to be a number (i.e. auto increment), then you may want to rethink using InnoDB. The primary key is replicated for every index on an InnoDB table. So if you have a large primary key and a few other indexes, your InnoDB table will get very large very quick.

Crocodile

At risk of sounding like a broken, fanboy record - this is why whenever I get to make the call I develop against PostgreSQL. The implementation of table partitioning is awesome. – Cortes

Answer 4

There are a few features that MySQL only has implemented for MyISAM (such as native fulltext indexing).

That said, InnoDB is still typically better for most production apps.

Magbie

that's also a valid scenario, although an external search engine (lucene, sphinx) is probably a better solution to use in these cases – Daciadacie

Answer 5

Also: Full-text search in mySQL is only supported in myISAM tables.

Throckmorton

Answer 6

MyISAM has a very simple structure, when compared with InnoDB. There is no row versioning, there's one file per table and rows are stored sequentially. However, while it supports concurrent inserts (SELECTs and 1 INSERT can run together), it also has table-level locks (if there are 2 INSERTs on the same table, 1 has to wait). Also, UPDATEs and DELETEs are slow because of the structure of the data files.

MyISAM doesn't support transactions or foreign keys.

Generally, MyISAM should be better if you work on general trends (so you don't care about the correctness of individual rows) and data is updated by night or never. Also, it allows to move individual tables from one server to another, via the filesystem.

InnoDB supports very well concurrency and transactions. Has a decent support for fulltext and an almost-decent support for foreign keys.

Trichocyst