What should I use MYISAM or INNODB?

I get this question allot, and my response is "it depends", people rarely like this general response. To pick a storage engine, the user must understand the gains and losses of using the storage engine, basically understand what INNODB and MYISAM do, and how they behave under different load patterns.

When picking a storage engine I go through a checklist-below is a quick list to get an idea of the thought process.

Do you require transactions?

If yes use INNODB else you may still want to use INNODB?

Are you doing a lot of big queries that Scan 20-30% of the rows?

If yes use MYISAM. It's better at doing large queries where the query requires a full table lock. INNODB will lock each row as it scans through it which hurts query throughput.

Are you building an app to store 1 row and access said row really fast and at a high concurrency?

See Tickets explanation of the example of an application that does this. Innodb hits a lock bottle neck when operating on the same row from many different threads, so it may not be advisable to use INNODB.

Are you building an app that stores a lot of blob data?

Now this is a grey area. With INNODB native zlib compression in 5.1+ INNODB may be a good choice over myISAM, while myISAM historically uses less diskpace then its INNODB counter-part. So, don't be afraid to create a single INNODB table and 100s of MYISAM tables that hold the blob data for INNODB.

Why do this? Because a single table in MYISAM will likely lock slowing down blob insertion / removal.


Do you require a lot of reads and writes where the ratio is not 90% reads 20% writes but more like 60/40?

Use INNODB over myISAM. If you don't believe me take a look at

show global status like 'table_locks_waited';   
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| Table_locks_waited | 82721 |
+--------------------+-------+

These are generic common questions that personally help me find out whats best to use. In reality to pick the correct storage engine, you should experiment and find out whats best for your app. Understand the storage engine-do some testing, then its easy to pick which is best.