If you’re new to MySQL, you’ve probably run into a term you haven’t seen before: Database engines. Database engines (also called “storage engines”)` are simply different ways of storing data. Different engines offer various benefits and limitations.
MySQL was released in 1995 with the MyISAM database engine. This was the default storage method for MySQL until it was replaced with InnoDB in MySQL 5.5 in 2009.
One big limitation of MyISAM was that it was not ACID compliant because it did not support transactions. InnoDB supports transactions and also introduced foreign keys and other features.
The database engine can be specified at the instance level, or down to the individual table level. You can see what engines are available in your MySQL instance by executing ‘SHOW ENGINES;’. Note that the SUPPORT column have the value ‘DEFAULT’ for one row. This is your default database engine, and with very rare exceptions, should be the ‘InnoDB’ engine.
When you create new tables in MySQL, they will be created using the default database engine. This can be changed by using the “ENGINE=” clause on the “CREATE TABLE” statement. It’s rare that you would need to do this, but it does give you great flexibility.
A few of the interesting default engines are:
CSV – Stores data for a table in a comma-separate file in the operating system. This is a great method for loading data exported from Excel or other programs that use CSV format. You can create a table that simply reads from the file. Note that indexes and transactions are not supported. But, this may greatly simplify the importation of data.
BLACKHOLE – If you’re familiar with Linux, you know that the device /dev/null, the null device, is a special file that simply discards whatever is written to it. Tables that are created in this database engine will simply discard any data written to them.
MEMORY – Short-term tables, such as temp tables, can be created in memory. These will obviously be very fast, but cannot hold extremely large amounts of data.
PERFORMANCE_SCHEMA – This database engine is used to track database performance. Many of the statistics shown with the various SHOW statements report data from these tables.
As said earlier, there are some features, such as transactions, indexes, foreign keys, and others that are limited to specific database engines. You may find a use for some of these lesser-used engines, but for almost all of your work, the InnoDB engine is the best engine to choose.
It’s also worth noting that some features of MySQL, such as Galera clustering, require that all tables use the InnoDB engine.
Think of a database engine as how data is stored, whether it is in memory, on disk, or the format that is stored. It’s not often you will need to use an engine other than InnoDB, but it’s worth knowing what the engines are and how they might be used.
If you are a DBA new to MySQL and learning the basics, check out Idera’s SQL Diagnostic Manager for MySQL. Through graphs, dashboards, monitors, and other features, it provides you with insight into how your database is performing. You can start your own free trial here.