Selecting table storage engines

Selecting Table Storage Engines

MySQL supports many different storage engines for its tables, each with its own advantages and disadvantages. While all of MySQL’s storage engines are reasonably efficient, using the wrong storage engine can hinder your application from achieving its maximum possible performance. For example, using the ARCHIVE engine for a table that will see frequent reads and writes will produce significantly slower performance than using the MYISAM engine for the same table.

To help you choose the most appropriate engine for your table, the following sections discuss each of these engines in greater detail.

The MyISAM Storage Engine

The MyISAM storage engine extends the base ISAM type with a number of additional optimizations and enhancements, and is MySQL’s default table type. MyISAM tables are optimized for compression and speed, and are immediately portable between different OSs and platforms (for example, the same MyISAM table can be used on both Windows and UNIX OSs). The MyISAM format supports large table files (up to 256TB in size) and allows indexing of BLOB and TEXT columns. Tables and table indexes can be compressed to save space, a feature that comes in handy when storing large BLOB or TEXT fields. VARCHAR fields can either be constrained to a specific length or adjusted dynamically as per the data within them, and the format supports searching for records using any key prefix, as well as using the entire key.

Because MyISAM tables are optimized for MySQL, it’s no surprise that the developers added a fair amount of intelligence to them. MyISAM tables can be either fixed-length or dynamic-length. MySQL automatically checks MyISAM tables for corruption on startup and can even repair them in case of errors. Table data and table index files can be stored in different locations, or even on different file systems. And intelligent defragmentation logic ensures a high-performance coefficient,

even for tables with a large number of inserts, updates, and deletions. Large MyISAM tables can also be compressed, or “packed,” into smaller read-only tables that take up less disk space, with MySQL’s myisampack utility.

The InnoDB Storage Engine

The InnoDB storage engine has been a part of MySQL since MySQL 4.0. InnoDB is a fully ACID-compliant and efficient table format that provides full support for transactions in MySQL without compromising speed or performance. Fine-grained (row – and table-level) locks improve the fidelity of MySQL transactions, and InnoDB also supports nonlocking reads and multiversioning (features previously only available in the Oracle RDBMS). InnoDB tables can grow up to 64TB in size.

Asynchronous I/O and a sequential read-ahead buffer improve data retrieval speed, and a “buddy algorithm” and Oracle-type tablespaces result in optimized file and memory management. InnoDB also supports automatic creation of hash indexes in memory on an as-needed basis to improve performance, and it uses buffering to improve the reliability and speed of database operations. As a result, InnoDB tables match (and, sometimes, exceed) the performance of MyISAM tables.

InnoDB tables are fully portable between different OSs and architectures, and, because of their transactional nature, they’re always in a consistent state (MySQL makes them even more robust by checking them for corruption and repairing them on startup).