MyISAM vs InnoDB: which one to use in MySQL?

MySQL supports several different types of Table Engines, and the two most commonly used are MyISAM and InnoDB.

In brief, which one we should use when?

Here is a quick comparison table:

Frequent INSERT, UPDATE, DELETE queries
Frequent SELECT queries
Full-text search (MySQL doc)
Transactions and ACID properties
Concurrent operations on the same table

The last row is due to the fact that MyISAM uses table-level locking, while InnoDB supports row-level locking. This means that if your database will have to deal with frequent writing operations, InnoDB will probably be your best choice.

Talking about memory usage, MyISAM has specific buffers for indexes and uses the OS disk buffer for caching other data, while InnoDB has a buffer pool for all data, and buffers as much data as possible in memory. If you can fit the entire DB in memory, InnoDB will never read from disk.