MySQL Storage Engines

MySql database is the world's most popular open source database because of its fast performance, high reliability and ease of use.
In this article I'm going to show you, what are the Storage engines available in mysql and how to use them.

What is a Storage Engine
Data in mysql stored in variety of different techniques.Each of this techniques have different mechanism, indexing facility, locking levels. By choosing most appropriate technique can gain additional speed and functionalities to manipulate data.

This flexibility to choose how your data is stored and indexed is a major reason why MySql is so popular than the other database system. Other database systems support only a single type of database storage.


Available Engines

Can determine the list of engines using

mysql>show engines

it will show the list of available engines.


Using and Engine

Specify the engine type when creating the table.

CREATE TABLE DEMO (ID int,title char(20)) ENGINE=INNODB

Altering the existing table

ALTER TABLE demo ENGINE = MyISAM

Differentiating the Engines


To compare the storage engines we need to know some core functionalities.

• Field and Data Types

          All Engines supports common data types (integer,real,character) but not all engines support other data types such as BLOB (binary large object) or Text.

• Locking

         How access and update to information are controlled. When object in the database is locked for updating, other process cannot modify the data until the update has complete.

There are 3 types of levels. Table locking is the most commonly level and it locks the entire table during and update. Ex : MyISAM 

Page level locking lock data according to the page of information that is being uploaded.

Row level locking provide the concurrency. Only individual rows within a table are blocks. That means many applications can update different rows with in same table. InnoDB supports row level locking.

• Indexing

        Increase the performance when searching and recovering data from database.  Some storage engines not support but some are support.

• Transaction

       Provide data reliability during update or insert information. For example transferring information from one account to another we use transaction to ensure both debit from account and credit to the other account completed successfully. If the process is failed cancel the transaction and the changes will lost.  

MySQL Storage Engines

MyISAM

• Default engine
• Use table level locking
• lacks of transaction capabilities.
• Large number of data writes(insert/update) can cause problems
• Design for read data.
• 64 index per table

InnoDB

• Support all functionalities of MyISAM and add transaction function
• Support transactions
• Can work with very large datasets.
• Some data types cannot stored

Memory (Heap)

• Store data in memory
• If the mysql server shutdown data will lost
• Not good for long term use
• Fast performance

Merge

• Allow to combine tables into one table
• Can only merge MyISAM tables
• Table locking level
• No transaction support

CSV

• Store data form of CSV(Command Seperated values) file.
• Not recommend to store large data.
• Portable but no indexing
• Easiest method for data exchange

BLACKHOLE

• Not store any data. Can do insert and update but not store any data
• Can create indexes

Berkeley DB

• Hash based storage mechanism
• Data accessing is very fast(quickest)
• Support transaction
• Use page locking
• Data recovery is a problem
• 31 index per table

Here is the brief summary of mysql storage engines. Choose a better storage engine to get much performance.