MySQL Storage Engines

Which one of the MySQL Storage Engines you should use is one of the basic design decisions you must make as DBA or application developer. As you probably know, storage engines are associated with specific table types, and can have a great effect on how you will implement your application.

MySQL storage engines are provided in a greater variety than most other databases.

In order to make an informed decision about what storage engine to use, you need to know what capabilities you will need in your application and know which engine can best support that functionality. To make the most of MySQL storage engine features, you may even use different engines for different parts of your application.

To see the MySQL storage engines you have available on your server, use the show engines statement:

As you can see, there are quite a few possibilities. However, only if DEFAULT or YES appears in the “Support” column is an engine available for use. As luck would have it, it's highly unlikely that you would be interested in any of the unsupported engines on this list, with the possible exception of the BerkleyDB engine. Let's discuss the available engines listed here.

The MEMORY storage engine allows the creation of a table in which all of the data is held in memory. The fact that the data is stored in memory means that it is not persistent. If the server shuts down for any reason, the data is lost. So this will not be a table in which you will store irreplaceable data.

Also, you need to make sure you have enough RAM on your machine to meet its storage needs. Despite this, MEMORY tables are useful when used wisely. Retrieving data from memory is much faster than retrieving it via disk I/O. MEMORY tables are often loaded with data selected from several other disk-based tables in order to use the superior performance to sort through the combined data. It is not recommended to use MEMORY tables for extended periods because of their transitory nature.

The MRG_MYISAM, or MERGE table as it is often called, can incorporate multiple tables with identical structures and allow the tables to be queried as one large table.

This is rather handy if your application produces identical tables every day and you want to perform aggregate queries on them, or if you have users on the same server using different but identically structured tables.

When you create a MERGE table you specify a “union” or list of tables are to be accessed through the MERGE table. The limitations of this engine are that the tables must be identical in structure and MERGE only works with MYISAM tables.

When we consider the choice of a storage engine for the bulk of our application data processing, the vast majority of the time we have two options in mind - should we use the default MYISAM storage engine, or do we need the added functionality of the InnoDB engine. In general, MYISAM tables perform more quickly than InnoDB tables and are easier to configure, but InnoDB provides functionality that MYISAM cannot.

Let's take a broad look at a few important features and see how these two engines stack up against one another.

• Speed - For most applications, MYISAM will provide superior performance. There are two main reasons for this. First, MYISAM does not have the overhead that added features, transaction support in particular, impose upon InnoDB. Secondly, MYISAM has superior indexing capabilities to InnoDB, which can greatly affect query speed. This is not to suggest that InnoDB is slow - it is a high performance engine. However, when the added functionality of InnoDB is not needed, MYISAM will most likely provide a more responsive application.
• Transactions - If your application requires transactions, your choice is InnoDB. MYISAM does not support transactions.
• Locking - This issue is important in an application under a heavy load, and there are differences in the way the two MySQL storage engines handle it. MYISAM uses table level locking. This means that when data in a table is being modified by one process, the entire table is locked. Other processes seeking to update data must wait, even if the data they want to update is in a different row in the table. InnoDB uses row level locking, in which only the row of the table that is being updated is locked. As you can imagine, on a busy server, this allows a far greater number of user requests to be processed in a given amount of time.
• Foreign keys - MYISAM tables do not support foreign keys. This is not an insurmountable disadvantage. Some programmers prefer to maintain consistency between tables through their application code anyway. Of course, I consider this a very unfortunate policy, with regards to lifetime consistency.
• Full text searches - If your application requires full text searches, you need to use MYISAM for this.
• Data types - MYISAM provides a greater variety of data types. For example, the BLOB type is not available on InnoDB.
• Ease of configuration - MYISAM is a bit simpler to configure and optimize.

Here are some example statements for setting engine type when tables are created. To create a simple InnoDB table:

The syntax for creating a MYISAM table is the same, except of course ENGINE = INNODB would be replaced by ENGINE = MYISAM.

To change a table from MYISAM to InnoDB, (or vice versa) use the ALTER TABLE statement.

Before performing this operation though, be aware that data types not available in InnoDB will not transfer. In the reverse case, changing the MySQL storage engine from InnoDB to MYISAM, foreign keys and any other features dependent upon the InnoDB storage engine will be broken.

To create a merge table use the following syntax:

Return to MySQL DBA