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.
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:
Free eBookSubscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:
What visitors say...
"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.Read more Testimonials
I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."
Theory & Practice
SQL Server DBA
Install SQL Server
Database Normalization eBook:
Copyright © www.databasedesign-resource.com / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: www.databasedesign-resource.com does not warrant any company, product, service or any content contained herein.
Return to top
The name Oracle is a trademark of Oracle Corporation.