MySQL/Table types

Every table is a logical object in a database; but it also needs to physically store its data (records) on the disk and/or in memory. Tables use a Storage Engine to do this. SE are plugins which can be installed or uninstalled into the server (if they're not builtin).

Many operations are requested by the server but physically done by the SE. So, from the SE we choose for a table affects performance, stability, LOCKs type, use of the query cache, disk space required and special features.

In some future versions of MySQL, partitioned tables will be able to use different SE for different partitions.

Let's see which Storage Engine is good for which uses.

Storage Engines

MyISAM and InnoDB

MyISAM does table level locking, while InnoDB does row level locking. In addition to foreign keys, InnoDB offers transaction support, which is absolutely critical when dealing with larger applications. Speed may suffer, particularly for inserts with full transaction guarantees, because all this Foreign Key / Transaction stuff adds overhead.

The default table type for MySQL on Linux is MyISAM, on Windows, normally InnoDB. MyISAM uses table level locking, which means during an UPDATE, nobody can access any other record of the same table. InnoDB however, uses Row level locking. Row level locking ensures that during an UPDATE, nobody can access that particular row, until the locking transaction issues a COMMIT. Many people use MyISAM if they need speed and InnoDB for data integrity.

MyISAM

• Pros
  • Fulltext search is currently only available with MyISAM tables
  • Geometric datatypes
  • Sometimes faster reads
  • All numeric key values are stored with the high byte first to allow better index compression
  • Internal handling of one AUTO_INCREMENT column per table is supported. MyISAM automatically updates this column for INSERT and UPDATE operations. This makes AUTO_INCREMENT columns faster (at least 10%)

• Cons
  • Table (not row) level locking only
  • No foreign keys constraints (but planned for MySQL 6.x)
  • Slower table checking and restarts after power loss, an issue for those who need high availability

InnoDB

• Pros
  • Provides MySQL with a transaction-safe (ACID compliant) storage engine that has commit, rollback, and crash recovery capabilities
  • XA transactions
  • Foreign keys
  • Row level locking
  • Maintains its own buffer pool for caching data and indexes in main memory
  • Faster for some workloads, particularly those where physical ordering by primary key helps or where the automatically built hash indexes speed up record lookups
  • Tables can be of any size even on operating systems where file size is limited to 2GB.
  • Fast and reliable recovery from power loss.

• Cons
  • Data takes more space to store
  • ACID guarantee requires full sync to disk at transaction commit, can be turned off where speed is more important than full ACID guarantees.
  • Data Versioning and transactions add overhead to table management.
  • They can lead to high memory requirements to manage large numbers of locks used in row locking.
  • Indexes are slow to build when they're added after a table has been created. Indexes should therefore be created when data is bulk-loaded.

Overall, InnoDB should be used for with applications that rely highly on data integrity or need transactions, while MyISAM can be used where that is not required or where fulltext indexing is needed. Where speed is more important, both should be tried because which is faster depends on the application.

Drizzle, a MySQL's fork supported by Sun Microsystems, uses InnoDB as its default engine and doesn't support MyISAM.

Merge Table

Synonyms: Merge, MRG_MYISAM

• A MERGE table is a collection of identical MyISAM tables that can be used as one.
• Identical means that all tables have identical column and index information, no deviation of any sort is permitted.

CREATE TABLE mumbai (first_name VARCHAR(30), amount INT(10)) TYPE=MyISAM
CREATE TABLE delhi  (first_name VARCHAR(30), amount INT(10)) TYPE=MyISAM
CREATE TABLE total  (first_name VARCHAR(30), amount INT(10)) TYPE=MERGE UNION=(mumbai,delhi)

Merges can be used to work around MySQL's or system's filesize limits. In fact those limits affect single MyISAM datafiles, but don't affect the whole Merge table, which doesn't have a datafile.

In the past, in some cases Merge and MyISAM could be used to replace views, which were not supported by MySQL. Merge could be used as a base table and MyISAM tables could be used as views containing part of the base table data. A SELECT on the Merge table returned all the effective data. View support was added in MySQL 5.0, so this use of Merge tables is obsolete.

MEMORY / HEAP

HEAP is the name of this table type before MySQL 4.1. MEMORY is the new, preferred name.

This engine is introduced in version 3.23.

BDB

Synonyms: BDB, BerkleyDB

BDB has been removed from version 5.1 and later due to lack of use.

BerkeleyDB is a family of free software embeddable DBMS's developer by SleepyCat, a company which has been acquired by Oracle. SleepyCat provided a Storage Engine for MySQL called BDB.

BDB supports transactions and page-level locking, but it also has many limitations within MySQL.

BLACKHOLE

Discards all data stored in it but does still write to the binary log, so it is useful in replication scale-out or secure binlog-do filtering situations where slaves aren't trustworthy and for benchmarking the higher layers of the server.

Miscellaneous

For completeness, other storage engines include:

• CSV: simple Comma-Separated Values engine, that uses the CSV format to store data. Used to share database with other CSV-aware applications maybe? Due to the simple nature of its format, indexing is not available.
• EXAMPLE (a stub for developers)
• ISAM (for pre-3.23 backward compatibility, removed in 5.1)

Metadata about Storage Engines

You can get metadata about official MySQL Storage Engines and other Storage Engines which are present on your server, via SQL.

SHOW STORAGE ENGINES

Starting from MySQL 5.0, you can get information about the Storage Engine which you can use using the SHOW STORAGE ENGINES statement.

SHOW STORAGE ENGINES

The STORAGE word is optional. This command returns a dataset with the following columns:

• Engine - Name of the Storage Engine.
• Support - Wether the Storage Engine is supported or not. Possible values:
  • 'DEFAULT' - it's supported and it's the default engine;
  • 'YES' - supported;
  • 'DISABLED' - it has been compiled, but MySQL has been started with that engine disabled (possibly with options like --skip-engine-name);
  • 'NO' - not supported.
• Comment - Brief description of the engine.
• Transactions - Wether the engine supports SQL transactions. Added in MySQL 5.1.
• XA - Wether the engine supports XA transactions. Added in MySQL 5.1.
• Savepoints - Wether the engine supports savepoints and rollbacks. Added in MySQL 5.1.

INFORMATION_SCHEMA `ENGINES` table

`ENGINES` is a virtual table within the INFORMATION_SCHEMA database. It can be used to get information about Storage Engines. Its columns are the came which are returned by the SHOW ENGINES statement (see above).

ENGINES has been added in MySQL 5.1.5.

HELP statement

If you want more info about an official MySQL Storage Engine, you can use the HELP command:

HELP 'myisam'

If you are using the command line client, you can omit the quotes:

help myisam \g

Changing the Storage Engine

SQL

When you want to create a table using a given Storage Engine, you can use the ENGINE clause in the CREATE TABLE command:

CREATE TABLE ... ENGINE=InnoDB

If the ENGINE clause is not specified, the value of the storage_engine variable will be used. By default it's MyISAM, but you can change it:

SET storage_engine=InnoDB

Or you can modify the value of default-storage-engine in the my.cnf before starting the MySQL server.

You can also change the Storage Engine of an existing table:

ALTER TABLE `stats` ENGINE=MyISAM

mysql_convert_table_format

mysql_convert_table_format is a tool provided with MySQL, written in Perl. It converts all the tables contained in the specified database to another Storage Engine.

The syntax is:

mysql_convert_table_format [options] database

database is the name of the database in which the program will operate. It's mandatory.

Options are:

--help Print a help and exit.

--version Print version number and exit.

--host=host The host on which MySQL is running. Default: localhost.

--port=port TCP port.

--user=user Specify the username.

--password=password Specify the password. As it is insecure (it's visible with the coomand top, for example), you can use an option file, instead.

--type=storage_engine The storage engine that the tables will use after conversion.

--force Don't stop the execution if an error occurs.

--verbose Print detailed information about the conversions.

Example:

mysql_convert_table_format --host=localhost --user=root --password=xyz970 --force --type=InnoDB test

This command specifies access data (localhost, username, password) and converts all tables within database `test` into InnoDB. If some tables can't be converted, the script skips them and converts the others (--force). Italic text