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 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;