MySQL storage engines

Home - Tutorials - MySQL

In this article I will summarise the MySQL storage engines (table types) to help you select the right one for your needs.

Tutorial info:

Bookmark MySQL storage engines

Step 1 - MySQL storage engines

MySQL storage engines

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:

Code: 
CREATE TABLE demo
(
    username VARCHAR(50) NOT NULL,
    age SMALLINT NOT NULL
) ENGINE=MyISAM;
Database F1

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.

In this article I will focus only on the most commonly used types, listed above.

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.

I hope this small introduction helps you a bit to decide which storage engine to select for your project. 

Tags: mysql storage engine, storage engine, mysql table type, table type, mysql, storage, engine,

©2007 Database F1 - Help in Database, get Database code, scripts, tutorials!