MySQL: Engine Selection

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

Engine Type Usage Pros Cons
ISAM Designed for applications, where:
Queries > Updates
Very fast read operations – Doesn’t support transactions
– Isn’t fault-tolerant
MyISAM – 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;
Advertisements
This entry was posted in Linux, Server and tagged , , , . Bookmark the permalink.