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. 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 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;
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)