Choosing a table type

By default, MySQL creates tables as MyISAM, which is very fast for querying data but slow for writing and also does not support some helpful features such as transactions. MyISAM is the default table type for MySQL, but you can choose from two other table types: BDB and InnoDB, both of which are transaction-safe. Of these two, InnoDB is a great deal easier to work with

There are benefits to choosing MyISAM over a transaction-safe table, particularly:

• It is faster

• It uses less memory and less disk space

Having said that, there are also benefits to choosing a transaction-safe table type over MyISAM, particularly:

• It is much safer. If your server crashes or powers off because someone pulled the plug out, a transaction-safe table can recover.

• You can rollback changes if you decide you do not want them

• InnoDB can, occasionally, be faster than MyISAM, particularly when handling a mix of updates and reads simultaneously

• If problems are encountered during a query, an automatic rollback is initiated to save the data.

You can assign each of your tables a different type and mix and match freely depending on whether you need security or performance. However, note that by default MyISAM is chosen for the table type, which means that if you request InnoDB and you do not have it installed or enabled, MySQL will instead make a MyISAM table. This makes things somewhat tricky if you do not notice that it has done that, but luckily there is an SQL command to help this, SHOW TABLE STATUS, which prints out table information for all tables in the currently selected database.

Running this command will print out a lot more information than you probably want - generally it is the first three columns that are of most interest. Column two tells you whether it is a MyISAM table, an InnoDB table, or another type, and column three tells you whether each table has a fixed row format or a dynamic row format.

As mentioned already, MyISAM is the default table type, which means that if you just create a table as per usual it will be MyISAM:

CREATE TABLE foo (ID INT);

To create a table as InnoDB, simply specify that at the end of the query, like this:

CREATE TABLE foo (ID INT) TYPE = InnoDB;

Similarly, you can be specific about your wish to have MyISAM tables, like this:

CREATE TABLE foo (ID INT) Type = MyISAM;

If you create a table of one type and later would like to change it to another type, use the ALTER TABLE command, like this:

ALTER TABLE foo Type = InnoDB;

As it is so easy to change between MyISAM and InnoDB, you should be able to run benchmarks on your system using both table types and figure out which suits your task the most.

Copyright ©2015 Paul Hudson. Follow me: @twostraws.