Thursday, November 10, 2011

Types & Comparison between MySQL database Storage Engines


MySQL is an open source database and used by the most of the database administrator. It uses database storage engines as a handler to handle different MySQL database tables. There are several storage engines supported by MySQL like MyISAM, InnoDB, Merge, Memory, Archive, CSV, Example, Federated and many more. All of them have their own functionalities and characteristics.

What is the default storage engine? The point comes in your mind. If database administrators do not select the type of storage engine for the database then what is the default storage engine for the database. The answer is InnoDB. It is the default storage engine.

Applied For: After release MySQL 5.5, InnoDB will be the default storage engine.

How to Change Storage engine? A database administrator can change the storage engine for database from the pluggable storage engine. Pluggable storage engine gives users flexibility to select the best fit for their specific case.

InnoDB storage engine is the most used storage engine in comparison of other storage engine. It is used for financial systems, health care applications, telecommunications, retails built on MySQL database. This storage engine is structurally designed to handle transactional related applications that needs crash recovery, high response time, concurrency, and integrity.

Comparison between InnoDB & MyISAM Storage engine: Each storage engine has its own specific characteristic and benefits. Beyond the performance, there are various other factors that suggest for a particular storage engine.

Features
InnoDB
MyISAM
ACID Transactions
Yes
No
B-Tree Indexes
Yes
Yes
Crash Safe
Yes
No
Clustered Index
Yes
No
Storage Limits
64TB
256TB
Foreign Key Support
Yes
No
Full Text Search Indexes
No
Yes
Backup/Point-in-time Recovery
Yes
Yes
Data Cashes
Yes
No
Others



Note: I have tested all above comparison scenarios but there may be many more comparison between these two storage engines.

Summary: InnoDB is default and most used storage engine by MySQL database administrators. It offers efficient ACID transactional capabilities. Now database administrator can create application in the InnoDB storage engine without altering default configuration settings.

About Author: Author is a technical writer and had written several article on MySQL recovery scenarios. You can reach him through comment.

Comments: Share your feedback through comments. I always welcome your comments.

No comments:

Post a Comment