MySQL Server Engines - What They Are, and When to Use Which One
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.
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.
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.
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 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.
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:
- Do you need transactions? InnoDB is your only option.
- Are most of the accesses to this table reads? MyISAM will give you good performance and ease of management.
- 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)