How to store, index and retrieve data through MySQL Storage Engines?

MySQL is a low cost, open source and extremely popular relational database management system. To a new user it may appear that reason of this immense popularity lies primarily in having to spend no or little money. But for any product to become successful at this scale, where so many websites are being driven by MySQL, there must be strong reasons besides low cost of ownership. One of the most important factors, which made this product so popular, is its flexible storage engine architecture.

A storage engine is a set of methodologies, procedures and feature set, using which data could be stored, indexed, retrieved and utilized. MySQL supports many storage engines, each of which can be used to serve a particular situation in the best possible manner like requiring greater speed, handling lots of temporary data, handling transaction-safe tables and nontransaction-safe tables.

Here is a short description of some of the prominent storage engines:

MyISAM: This storage engine is the one, which is most commonly used in most of the web applications. Even in case of data warehousing this storage engine has been found quite effective.

InnoDB: This is one of the most powerful storage engines. It is primarily used to ensure a transaction safe environment with its Commit, Rollback and crash recovery features. It also has got row locking features, which helps in achieving multi-user concurrency, which turns out to be extremely vital in case of real time applications like airline ticket reservations.

Memory: This storage engine is used in those cases where speedy access to data is required. Here entire data is stored in RAM, which ensures swift access and thus saves lots of invaluable time.

Merge: If you are working in very large database environments (VLDB) meant for operations like data warehousing and data mining then this storage engine would be the tailor made for your needs because here similar tables can be logically grouped as one object, which makes it a lot easier to store and retrieve data from the various sources.

Archive: As evident from its very name, when you want to store and retrieve historical data of your enterprise then this storage engine provides the most ideal environment.

Federated: This storage engine is perfect for the multi location enterprises, where data is stored in different physical servers. Here various MySQL databases could be logically linked to form one unified database.

There are other storage engines like NDBCLUSTER, CSV, Blackhole and example, which also can be used in variety of other applications and situations.

Author: ucavik