What is the Storage Engine in MySQL
Following Question Based on MySQL Storage Engine covered in this Section:
- What is storage engine?
- How many types of storage engines are there in MySQL?
- What are the features of Different type of storage engines?
- Why Memory is faster than MyISAM and InnoDB?
- Which one is the Transaction Compatible Engine?
- Which are the storage engine in MySQL , do not use Indexing?
- What are the main difference between in MyISAM and InnoDB?
- 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:
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 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 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.
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 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 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.