Understanding Useful Table Engine Types

Published October 28th, 2008

MySQL has a number of table engine types that have their pros and cons depending on usage. Even though there are tons of Web pages describing the details of these engines, we found most beginning to intermediate MySQL developers do not have a clear understanding of them; hence, they are not taking advantages of these engines as much as they should. This short article is an attempt to introduce the pros and cons of useful table engines available in MySQL.

Useful Table Engines

In our everyday use of MySQL for every Web project that we work on, we find the following table engines useful:

MYISAM

The default table engine that you get whenever you create a new table without specifying a table engine type.

By default entire table is locked to perform INSERT/UPDATE/DELETE queries

Pros	Cons
Optimization, repair and status checking tools available	No row-level locking available

InnoDB

You want to use it for transactions with roll back capability and also when automatic referential integrity is very import for your project.

Pros	Cons
Row-level locking is the best reason to use this when appropriate	CPU intensive engine — requires more CPU cycles and thus adds load to your server
Automatic referential integrity supported	By default, eats a lot of disk space as the default tablespace file does not shrink even when your data is deleted or truncated
Used widely for transaction queries with rollback requirements	Repairing is automatic; if automatic repairing fails, you are out of luck without professional help
Makes MySQL a bit-more acceptable to some Oracle, MSSQL DBAs :)	Owned by Oracle! :)

MEMEORY/HEAP

You want to use this table engine when performance of frequently requested data that rarely changes is important. Data such as product list, configuration, etc. are really good candidate for this table type. Watch out, you have to have lots of RAM and have a way to get/put data to/from physical tables when you start/restart/stop the MySQL server

MERGE

This is a really interesting engine that can make your database appear “fresh as new” every day. By using multiple tables in a merged manner, you can keep INSERT traffic coming to a node table and have an archiving process to move old data from one node to another. This can really help you speed up your database performance where SELECT traffic is best kept unlocked from INSERT traffic without using InnoDB.

Category: Concepts | Tags: