mysql-storage-engine

What is the Storage Engine in MySQL

Following Question Based on MySQL Storage Engine covered in this Section:

  1. What is storage engine?
  2. How many types of storage engines are there in MySQL?
  3. What are the features of Different type of storage engines?
  4. Why Memory is faster than MyISAM and InnoDB?
  5. Which one is the Transaction Compatible Engine?
  6. Which are the storage engine in MySQL , do not use Indexing?
  7. What are the main difference between in MyISAM and InnoDB?
  8. Which version of MySQL support Transaction?

MySQL Storage Engine

Storage Engine in MySQL is the bottom most layer and stores data in various formats. Based on their techniques and compatibility for different types of application we choose which one would be suitable. MySQL has number of storage engines. Few of them are transactional and few are non-transactional. Here is the list of popular storage engines in MySQL:

  1. MyISAM
  2. InnoDB
  3. Memory(HEAP)
  4. Archive
  5. CSV
  6. MERGE

MyISAM

This is a non-transactional storage engine and a table can hold up to 256 TB data. Before MySQL 5.5 MyISAM was the default storage engine, but after Introduction of transaction support InnoDB is the default storage Engine in MySQL. Data in MyISAM is compressed and optimized for quick access. Earlier it was known as ISAM. It can repair the corrupted tables itself on startup.

InnoDB

InnoDB is the most widely used storage engine now a days and used as a default storage engine in MySQL. It is transaction safe and compatible to ACID properties of database transactions. It supports all the DML, DDL and DCL operations. It can hold up-to 64 TB data in a table. It also uses compression techniques to store data.

MEMORY

Memory Engines store data in Memory. It uses the hash index techniques to store data and hence the access of data is comparatively faster than MyISAM and InnoDB. In Memory data is stored in the form of HEAP tables.

Archive

The engine is used for archiving the records. It can be used to archive the huge amount of data and records. It stores data in compressed format and when an access request is come it serves the data after decompressing the archives. We can not have Indexing in Archive engines so we have to scan all the data to get the particular records.

CSV

CSV is a well-known spreadsheet format. It is also used for Non-SQL based systems to store and retrieve data. Data is stored CSV format or comma separated formats. This is also a non-indexing technique. We can not have a NULL value in CSV. It also required to read full table to get a record. Hence it is slower than InnoDB and MyISAM.

MERGE

MERGE is storage engine that uses MyISAM tables as component and merged them to store as a single table. MERGE tables use Indexes of component tables, they do not have their own indexes. When we run DROP command then It does not drop tables in real, but the merger splits.

Dhananjay

Dhananjay

I’m Dhananjay — a free-spirit with a passion for technologies and blogging.

View all posts by Dhananjay →

7 thoughts on “What is the Storage Engine in MySQL

  1. These are truly wonderful ideas in on the topic of blogging. You have touched some good things here. Any way keep up wrinting.|

  2. Excellent post. I used to be checking continuously this blog and I am impressed! Extremely useful info specifically the last phase 🙂 I deal with such info much. I was looking for this particular info for a long time. Thanks and best of luck.

  3. I would like to thank you for the efforts you’ve put in writing this site. I’m hoping to see the same high-grade blog posts by you in the future as well. In truth, your creative writing abilities has inspired me to get my own, personal blog now 😉

  4. Hello there, just became aware of your blog thru Google, and located that it’s truly informative. I’m gonna be careful about Brussels. I’ll appreciate if you happen to continue this in future. A lot of folks might be benefited from your writing. Cheers!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.