# 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%)

```{=html}
<!-- -->
```
-   **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.

```{=html}
<!-- -->
```
-   **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*
