Hello guys, we are planning to start work on MySQL blogs. So after a long time, we are back with a new blog. This blog gives you the core and important information about MySQL storage engines. Let's begin.
Here we are going to learn about different types of database storage engines, the advantages of it. Every engine has its own specialty.
Types Of Storage Engines
- MyISAM
- MRG_MyISAM
- BLACKHOLE
- MEMORY
- InnoDB
- ARCHIVE
- CSV
- FEDERATED
InnoDB :
Now a days if you create any MySQL database table its has the default storage type is InnoDB, but before MySQL 5.5 its MyISAM. The default engine in MySQL. InnoDB has the support of transactions and locking mechanism.
When you create any table it will create three files to manage the details, one is for the format file, the second one is for the actual data file, and the last one is for indexes.
Memory:
The memory storage engine is useful to save temporary data. Now you know temporary data will get saved into the memory (RAM). You will get faster results if you save the records in memory instead of physical memory(DISK). If you use memory as an engine type then only format file will get created. Once you restart the server, all records get vanished and only the table structure keeps as it is.
Archive:
The archive storage engine type is used to store historical data and compressed data. You can archive the table data. This storage engine does not support the indexes. If a primary key was there you can not compress the data. So in that case, you have to remove the primary key, and then you can compress the data. Now in physical storage, there were two files are managed, one is a format file and the second one is ARZ (archive) file.
CSV :
As the name speaks its saves comma-separated values. This engine does not support NULLABlE values you have to add some records in each column. Now normally you want to export MySQL data into CVS then you Click on the Export button and then you export as CVS, now this will take time. Instead of doing this, you can use a CSV storage engine. Now CSV type of table will create three files in physical memory. One is a format file, one is a data file (CSV) and one is CSM (system metadata file). Now if you want to export then you just need to copy the data file and that's it. So easy to manage.
MyISAM:
MyISAM is fast and preferred for static and basic web and mobile applications. This engine type is not providing transaction supports. This engine type provides only table-level locking. Foreign key details are not allowed and MyISAM only supports only "FULL-TEXT SEARCH". It allows only one auto-increment column per column.
MRG_MyISAM:
Suppose you have two same tables with the same column name so here you can perform merging over that two tables. Now if you perform merging it'll get saved into the new table. Here if you see the physical memory, it'll create one MRG file and one format file only. After creating a merging table, if you insert a record in one table then it'll automatically get inserted into the new merged table.
BLACKHOLE:
This is a very interesting storage engine type, its store records in null value storage. So anything you will insert get disappear. How strange it that ????, but it's a beauty of it. Now you can use this storage engine based on your requirement ????.
FEDERATED:
This is a small type of storage engine. This will use to connect MySQL remotely. FEDERATED is a remote MySQL database without using any replication or cluster.
Conclusion :
Generally, if you are not specifying the storage engine type, it'll create default InnoDB if you are using MySQL >= 5.5 and it'll create MyISAM default. You can use a different storage engine for the project but you have an idea about its core working.
Actually, I regularly use and suggest to use InnoDB, CSV, and Memory for better performance. I hope you like this blog.
Thanks for reading ????!