MySQL: Storage Engines

MySQL has a nasty "gotcha" with the database storage engine.

MyISAM, which is pretty much unusable in a serious database use-case, is the default storage engine (prior to version 5.5.5), and to not use it, it is necessary to specify "default-storage-engine = innodb" either as an mysqld command line option or as an entry in the my.cnf file (below [mysqld].

If that is not done, then every table create will be in MyISAM by default.

In the case where the default storage engine is MyISAM, it would be necessary to add the following to every table creation statement:

CREATE TABLE table_name (

id int AUTO_INCREMENT PRIMARY KEY,
column_one int,

column_two varchar(16)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

To query tables for storage engine information, use the SHOW TABLE STATUS statement. Examples:

1. SHOW TABLE STATUS LIKE 'table_name'; (view the storage engine for a table)
2. SHOW TABLE STATUS WHERE Engine='InnoDB'; (view all tables using storage engine InnoDB)

To change an existing table to use InnoDB:

1. ALTER TABLE table_name Engine = InnoDB;

References

1. dev.mysql.com/doc/refman/5.1/en/show-table-status.html
2. dev.mysql.com/doc/refman/5.1/en/storage-engine-setting.html
3. dougalmatthews.com/articles/2008/sep/3/changing-default-storage-engine-in-mysql/
4. dev.mysql.com/doc/refman/5.5/en/storage-engines.html