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.