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

From WikiContent

(Difference between revisions)
Jump to: navigation, search
Current revision (22:36, 15 October 2009) (edit) (undo)
 
(2 intermediate revisions not shown.)
Line 11: Line 11:
=== 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.
+
This is the only (common) engine that supports ACID transactions and enforced referential integrity via foreign key constraints. It also offers row-level locks, fast writes, and good crash recovery. On the down side, it can be slower than MyISAM in a read-heavy environment.
=== Memory ===
=== Memory ===
Line 20: Line 20:
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.).
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.).
- 
-
You can learn more about the other engines – Merge, Federated, CSV, Blackhole, and Example – on the MySQL documentation site: http://dev.mysql.com/doc/
 
== Picking an Engine ==
== Picking an Engine ==
Line 38: Line 36:
CREATE TABLE my_table (….) ENGINE = InnoDB;
CREATE TABLE my_table (….) ENGINE = InnoDB;
 +
 +
You can change the default engine by adding this line to your my.cnf file:
 +
 +
default-storage-engine = InnoDB
If you need to change an existing table to a different engine you can use ALTER TABLE:
If you need to change an existing table to a different engine you can use ALTER TABLE:
Line 43: Line 45:
ALTER TABLE my_table ENGINE = MyISAM;
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.
+
Note that changing engines for a large table will take quite a while, and may consume a lot of disk in the process. If you can't spare the space, you may have to export the table, re-create it with the new engine, then re-load the data.
--[[User:Rsonnen|Rsonnen]] 10:13, 12 October 2009 (PDT)
--[[User:Rsonnen|Rsonnen]] 10:13, 12 October 2009 (PDT)

Current revision

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 and enforced referential integrity via foreign key constraints. It also offers row-level locks, fast writes, 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;

You can change the default engine by adding this line to your my.cnf file:

default-storage-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 in the process. If you can't spare the space, you may have to export the table, re-create it with the new engine, then re-load the data.

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

Personal tools