MySQL Storage Engines - MyISAM, InnoDB and others

MySQL has a feature called Storage Engines - it supports multiple independent storage engines that can be used as handlers for different table types. Each table can be stored in a way that is most optimized for that particular table.

With this system, the developer can choose how a table must be stored - he can chose which storage engine is best for that particular situation. For example in a content management system, the table containing the contents will be read more often - so we will chose a storage engine better optimized for reading data(eg. MyISAM). But in the case of the table storing the logs, it must be written more often - so we chose a storage engine that is better at writing for that table. Having multiple storage engine makes sure that you can get the best optimization for any given situation.

There are many who believe this is a Bad Thing. In most other DBMS, the Administrator don't have to worry about such details - the DBMS will take care of this. I don't want to take sides in this debate - as for many people, this has become a religious issue. Anyway, as I am writing a series on MySQL, I cannot ignore this very important feature of MySQL.

I have only used two of MySQL many Storage Engines - MyISAM and InnoDB. The rest, I have not uses yet. Someday.

MyISAM

MyISAM is the default storage engine for MySQL. If you create a table in MySQL, without paying any attention to select a specific engine, then that table will be created in MyISAM(by default). This storage engine is the best for high-speed storage and retrieval. It also supports fulltext searching capabilities.

MyISAM is supported in all MySQL configurations. This engine is used for Data warehousing, read-only archives optimized for bulk operations, etc. as it is the best solution when you want flexible index formats, big scans and has a small disk footprint for data and index. This is not suitable for a typical OLTP application because of table-level locking, bad crash durability and lack of ACID compliance.

InnoDB

The InnoDB storage engine provide transaction-safe tables(BDB also does this). InnoDB is also included by default in all MySQL 5.0 binary distributions. This engine supports...

• Row level locking
• MVCC
• Savepoints within transactions
• More features for typical OLTP applications.

One of the main advantage of this engine is that you can rollback to a previous version of the database using its COMMIT/ROLLBACK support. InnoDB engine is more reliable than MyISAM.

Memory

The MEMORY storage engine provides in-memory tables. The MERGE storage engine will let you handle many identical MyISAM tables as a single table. Like MyISAM, the MEMORY and MERGE storage engines handle non-transactional tables, and both are also included in MySQL by default.

The main advantages of the MEMORY Storage engine are...

• No disk footprint
• Extremely fast

This is most useful for...

• Summary tables
• Simulations
• Temporary operations(sessions etc)

CSV

The CSV storage engine stores data in text files using comma-separated values format. The main advantages of this approach are...

• Very simple - data stored in plain text files
• The database can be modified without the server
• Export/Import couldn't be easier.

Other Storage Engines

There are many other storage engines supported by MySQL. Some of them are...

• NDB Cluster
• ARCHIVE
• BLACKHOLE
• FEDERATED
• Falcon

If you are not satisfied with any of these engines, you can write your own engine.

Reference

• MySQL Storage Engines - 8 part series on MySQL Storage Engines
• Storage Engine (Table Types) - 15 part series
• MySQL AB :: MySQL Storage Engine Architecture, Part 1: An Overview
• Storage Engines and Table Types - MySQL Reference Manual

Filed Under...

• web
• development
• mysql
• database
• dbms
• storage
• engines