Which Storage Engine (table handler) to use?

MySQL 4.0 Standard comes with 3 main Storage Engines, MyISAM, HEAP, and INNODB. We are often asked, which of them is the best choice for particular application. The answer is that mixing storage engines would give best results in most cases.

Each storage engine has it's strong and weak sides.

MyISAM tables are really fast for select-heavy loads, while table level locks limit their scalability for write intensive multi-user environments. MyISAM tables have the smallest disk space consumption as well as such advanced features as full text index, merged and compressed tables.

INNODB tables allow you to use ACID transactions, have row level locking and consistent reads, which allow to reach excellent read write concurrency. InnoDB tables also have primary key clustering, which gives excellent performance in some cases. For referential constraints InnoDB tables offer foreign key support. Another good feature of InnoDB tables, which helps a lot in case the operating system has inefficient cache implementation, is that both index and data pages can be cached by MySQL. All updates have to pass through transactional engine in InnoDB, which often decreases performance compared to non-transactional storage engines. The outstanding feature of InnoDB tables is also automatic crash recovery - in case MySQL shutdown was unclean InnoDB tables will still recover to the consistent state, you will not have to check or repair them. For 24/7 type applications InnoDB tables could be chosen for HotBackup solution available - consistent backup can be taken without affecting application any way.

HEAP is lightning fast in memory storage engine. It is designed for maximum speed, however it has some limitations. The most important one is - data is lost at MySQL shutdown. Heap also uses hash indexes, which do not allow to use indexes for range queries and Heap doesn’t support varchar, blob and text columns. Heap has locks at table level, i.e. similarly as in MyISAM.

Table level locks are often blamed for being inefficient, however this is not that simple. The smaller granularity of locks you have, the larger amount of locks you have to set and check, which takes time, on other hand this leads to less false conflicts - operations which could be executed in parallel are found as conflicting, and one of them has to wait. This is one of the reasons why choice of storage engines in MySQL offers excellent performance.

How do you usually mix storage engines? The most obvious way possible - you know the operations, which are performed on the table and you select appropriate table type for it.

MyISAM tables are really good for large constant tables or logging tables (concurrent insert feature allows MyISAM tables to perform inserts even while table is being read). They are also often good choice for tables with relatively infrequent updates or fast selects - these will not lock the table for the long time and so will not reduce performance.

InnoDB tables are good for intensively updated tables, which can have many long selects running at the same time. They are also good choice for storing sensitive information, such as user registration information or financial data, and of course you should use these tables if you need ACID transactions. In case you have really large table with many indexes it could be worth to have it in InnoDB type - you will not have to recover the table in case of unclean shutdown, which could take hours.

Heap tables are best to be used for relatively small temporary tables, which are small enough to fit in memory. Also you might wish to use temporary tables for summary and cache tables, which you can easily and quickly regenerate.

In typical web application MyISAM table could be used for logging and search, InnoDB tables for registration information and banner system, while Heap table for temporary tables and pre generated news headlines and other data for high load pages.

Enjoy choice of storage engines for the best performance of your application with MySQL!