Extended Brain Storage

Server: MySQL Engine Selection

Posted on January 13, 2013

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 TypeUsageProsCons
ISAMdesigned for applications, where: Queries > Updatesvery fast read operationsdoesn't support transactions and isn't fault-tolerant
MyISAMthe default engine, extended ISAM format, uses a table-locking mechanism to optimize multiple simultaneous reads and writesMyISAMChk to repair database files, MyISAMPack to repair wasted spacedesigned for fast read operations
HEAPtemporary tables that reside only in memoryreading faster than ISAM or MyISAMdata is: volatile, will be lost if not saved
InnoDB and Berkley DBwhere transactional and foreign-key support neededtransactions, foreign-key supportslower 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;

Tags: #MySQL #engine #ISAM #MyISAM #InnoDB #Berkley DB

⏴ Previous Post Next Post ⏵