Storage engines in MySQL

When you create a new table in MySQL then there is the possibility to define the table type or storage engine at the end of your table definition. This is not a mandatory part of your table definition. If you don't define an engine then the default one will be used. Later you can change the storage engine by ALTER TABLE command.Here is an example how to create a table specifying a storage engine:

CREATE TABLE demo
{
username VARCHAR(50) NOT NULL,
age SMALLINT NOT NULL
} ENGINE=MyISAM;

Instead of ENGINE keyword you can also use TYPE, however the preferred one is the ENGINE as the TYPE is only for backward compatibility.

MySQL Storage engines

In MySQL you have the possibility to select from the following storage engines:

• MyISAM
• InnoDB
• MERGE
• MEMORY

In older MySQL version the BDB and ISAM table types were also available but in the new versions they are not more supported. Besides this in MySQL 6 there will be a new table type called Falcon.I

MyISAM
MyISAM is the default storage engine in MySQL. It is the improved replacement of the old ISAM table type. Using MyISAM storage engine every table is stored in 3 different files:

1. .frm file which stores the table structure
2. .MYD file, which stores the table data
3. .MYI file which is the index file

MyISAM storage engine is optimized for speed and supports extensive indexing. You can index BLOB and TEXT and also supports FULLTEXT indexes.

However MyISAM tables do not supports foreign key constraint and row level locking.

InnoDB
InnoDB is a transaction-safe storage engine in MySQL. Table data are managed by the InnoDB tablespace. These table type supports foreign key constraints and row level locking as well. However FULLTEXT indexes are not supported.

MERGE
The MERGE storage engine is a bit special. In this case the data are not stored in the MERGE table, but in the MyISAM tables from which the virtual MERGE table was made up.

MEMORY
In case of MEMORY storage engine the data are stored in the memory and are available only as long as the MySQL server is available. The MEMORY tables are very fast and so they are ideal for temporary tables.