MySQL Storage Engines

Many times we just thinking what should be the perfect storage engine for my application? So I come up with this useful topic, which I believe will certainly help you.

So question is what is storage engine:
A storage engine is what stores, handles, and retrieves information from a table. There is no "perfect" or "recommended" storage engine to use, but for most applications the MyISAM or InnoDB is fine.

It is essential to understand the features of each table type in MySQL so that you can use them effectively to maximize the performance of your databases.

For example, if you store a lot of log data you might want to use the ARCHIVE storage engine which only supports INSERT and SELECT.


Here I am listing the MySQL storage engines, you can use as per your application requirements-

MyISAM:
* Provides high-speed storage and retrieval
* Supports full text searching.
* It does table level locking (when insert,update and delete)

* Non-transactional.
* MySQL checks MyISAM tables for corruption and even repair them in case of errors.
* Manages non transactional tables.


InnoDB:

* Its design follows the ACID model, with transactions featuring commit, rollback, and  crash-recovery capabilities to protect user data.
* Supports FOREIGN KEY referential-integrity constraints.
* It also support row-level locking
* It stores data in clustered indexes which reduces I/O for queries based on primary keys.

MEMORY:
* Provides in-memory tables, formerly known as HEAP.
* It sores all data in RAM for faster access than storing data on disks.
* Useful for quick looks up of reference and other identical data.

MERGE:
* It enables users to have a collection of identical MyISAM tables to be handeled by a single table. "Identical" means that all tables have identical column and index information.

e.g. If you store sales transactions on your site in a daily table, and want to pull a report for the month, this would allow you to execute a query against a single table to pull all sales for the month.

ARCHIVE:
It is used for storing large amounts of data without indexes in a very small footprint.
This engine will only support INSERT and SELECT, and all information is compresses.
This makes it the perfect storage engine for logs, point of sale transactions, accounting, etc.

CSV:
It stores data in text files using comma-separated values.
As such this is not an ideal engine for large data storage, tables requiring indexing, etc.
The best use-case for this is transferring data to a spreadsheet for later use.

FEDERATED:
This storage engine enables data to be accessed from a remote MySQL database on a local server without using replication or cluster technology. When using a FEDERATED table, queries on the local server are automatically executed on the remote (federated) tables. No data is stored on the local tables.

BLACKHOLE:
It does not allow for any data to be stored or retrieved, is good for testing database structures, indexes, and queries. Retrievals always return an empty result.

BDB (BerkeleyDB):

The BDB handles transaction-safe tables and uses a hash based storage system. This allows for some of the quickest reading of data, especially when paired with unique keys. There are, however, many downfalls to the BDB system, including the speed on un-index rows, and this makes the BDB engine a less than perfect engine choice. Because of this, many people tend to overlook the BDB engine.