Server: MySQL Engine Selection

Every database designer has to decide which type of database engine is necessary to use for a specific type of data approach. This is a brief overview of the most common ones…

Brief Overview

Engine Type	Usage	Pros	Cons
ISAM	designed for applications, where: Queries > Updates	very fast read operations	doesn’t support transactions and isn’t fault-tolerant
MyISAM	the default engine, extended ISAM format, uses a table-locking mechanism to optimize multiple simultaneous reads and writes	MyISAMChk to repair database files, MyISAMPack to repair wasted space	designed for fast read operations
HEAP	temporary tables that reside only in memory	reading faster than ISAM or MyISAM	data is: volatile, will be lost if not saved
InnoDB and Berkley DB	where transactional and foreign-key support needed	transactions, foreign-key support	slower than the (My)ISAM

An example of engine type application in MySQL syntax (MyISAM).

CREATE TABLE tablename ( ... ) ENGINE=MyISAM;

To display the set up engine type:

SHOW TABLE STATUS FROM dbname;