MySQL Server Engines - What They Are, and When to Use Which One

From WikiContent

Revision as of 22:18, 15 October 2009 by Rsonnen (Talk | contribs)
Jump to: navigation, search

MySQL allows you to choose how your data gets stored and indexed via its pluggable storage engines. Different engines are tailored to offer specialized features (such as transactions) and/or to provide better performance under certain conditions.

Contents

Meet the Engines

The 5.x branch of MySQL ships with nine different engines, but only four are really intended for primary storage: MyISAM, InnoDB, Memory, and Archive.

MyISAM

This is MySQL’s default engine. It provides good general performance, is easy to administer, and supports very large tables (256TB). On the down side, it doesn’t support transactions, doesn’t enforce foreign key constraints, can only provide locking at the whole-table level, and is less stable in a crash than log-based engines like InnoDB.

InnoDB

This is the only (common) engine that supports ACID transactions. It also offers row-level locks, enforced foreign-key constraints, and good crash recovery. On the down side, it can be slower than MyISAM in a read-heavy environment.

Memory

Memory tables store all their data in RAM rather than on disk. This makes them very fast, but completely volatile. All data is lost whenever the server is stopped or crashes. The Memory engine is only usable for caches and other completely derived data that can be regenerated on demand.

Archive

The Archive engine compresses each row to save space. The tables only support SELECT and INSERT – you can’t update, replace, or delete rows. This engine is usually used to store infrequently-accessed historical data (log entries, etc.).

Picking an Engine

Ask yourself these questions whenever you need to pick an engine for a table:

  1. Do you need transactions? InnoDB is your only option.
  2. Are most of the accesses to this table reads? MyISAM will give you good performance and ease of management.
  3. Do you expect a very high volume of concurrent reads and writes? InnoDB will keep your writes from bogging down the reads.

So should you ever pick Memory or Archive? Not if one of the other engines will work. Both come with so many dangers and restrictions that they’re rarely useful.

Using an Engine

So how do you tell MySQL to use the engine you’ve picked? If you’re creating a new table, you can just tack it onto the CREATE TABLE statement:

CREATE TABLE my_table (….) ENGINE = InnoDB;

If you need to change an existing table to a different engine you can use ALTER TABLE:

ALTER TABLE my_table ENGINE = MyISAM;

Note that changing engines for a large table will take quite a while, and may consume a lot of disk space in the process.

--Rsonnen 10:13, 12 October 2009 (PDT)

Personal tools