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.
Can determine the list of engines using
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=INNODBAltering 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.
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.
Increase the performance when searching and recovering data from database. Some storage engines not support but some are support.
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
- 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
- Support all functionalities of MyISAM and add transaction function
- Support transactions
- Can work with very large datasets.
- Some data types cannot stored
- Store data in memory
- If the mysql server shutdown data will lost
- Not good for long term use
- Fast performance
- Allow to combine tables into one table
- Can only merge MyISAM tables
- Table locking level
- No transaction support
- Store data form of CSV(Command Seperated values) file.
- Not recommend to store large data.
- Portable but no indexing
- Easiest method for data exchange
- Not store any data. Can do insert and update but not store any data
- Can create indexes
- 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.