(mysql.info) storage-engines

 
 14 Storage Engines and Table Types
 **********************************

 
* myisam-storage-engine        The `MyISAM' Storage Engine
* innodb                       The `InnoDB' Storage Engine
* merge-storage-engine         The `MERGE' Storage Engine
* memory-storage-engine        The `MEMORY' (`HEAP') Storage Engine
* bdb-storage-engine           The `BDB' (`BerkeleyDB') Storage Engine
* example-storage-engine       The `EXAMPLE' Storage Engine
* federated-storage-engine     The `FEDERATED' Storage Engine
* archive-storage-engine       The `ARCHIVE' Storage Engine
* csv-storage-engine           The `CSV' Storage Engine
* blackhole-storage-engine     The `BLACKHOLE' Storage Engine
 
 MySQL supports several storage engines that act as handlers for
 different table types. MySQL storage engines include both those that
 handle transaction-safe tables and those that handle
 non-transaction-safe tables:
 
    * `MyISAM' manages non-transactional tables. It provides high-speed
      storage and retrieval, as well as fulltext searching capabilities.
      `MyISAM' is supported in all MySQL configurations, and is the
      default storage engine unless you have configured MySQL to use a
      different one by default.
 
    * The `MEMORY' storage engine provides in-memory tables. The `MERGE'
      storage engine allows a collection of identical `MyISAM' tables to
      be handled 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 `MEMORY' storage engine formerly was known as the
      `HEAP' engine.
 
    * The `InnoDB' and `BDB' storage engines provide transaction-safe
      tables. `BDB' is included in MySQL-Max binary distributions on
      those operating systems that support it. `InnoDB' is also included
      by default in all MySQL 5.0 binary distributions. In source
      distributions, you can enable or disable either engine by
      configuring MySQL as you like.
 
    * The `EXAMPLE' storage engine is a `stub' engine that does nothing.
      You can create tables with this engine, but no data can be stored
      in them or retrieved from them. The purpose of this engine is to
      serve as an example in the MySQL source code that illustrates how
      to begin writing new storage engines. As such, it is primarily of
      interest to developers.
 
    * `NDB Cluster' is the storage engine used by MySQL Cluster to
      implement tables that are partitioned over many computers. It is
      available in MySQL-Max 5.0 binary distributions. This storage
      engine is currently supported on Linux, Solaris, and Mac OS X
      only. We intend to add support for this engine on other platforms,
      including Windows, in future MySQL releases.
 
    * The `ARCHIVE' storage engine is used for storing large amounts of
      data without indexes with a very small footprint.
 
    * The `CSV' storage engine stores data in text files using
      comma-separated values format.
 
    * The `BLACKHOLE' storage engine accepts but does not store data and
      retrievals always return an empty set.
 
    * The `FEDERATED' storage engine was added in MySQL 5.0.3. This
      engine stores data in a remote database.  Currently, it works with
      MySQL only, using the MySQL C Client API. In future releases, we
      intend to enable it to connect to other data sources using other
      drivers or client connection methods.
 
 This chapter describes each of the MySQL storage engines except for
 `NDB Cluster', which is covered in  ndbcluster.
 
 When you create a new table, you can specify which storage engine to
 use by adding an `ENGINE' or `TYPE' table option to the `CREATE TABLE'
 statement:
 
      CREATE TABLE t (i INT) ENGINE = INNODB;
      CREATE TABLE t (i INT) TYPE = MEMORY;
 
 The older term `TYPE' is supported as a synonym for `ENGINE' for
 backward compatibility, but `ENGINE' is the preferred term and `TYPE'
 is deprecated.
 
 If you omit the `ENGINE' or `TYPE' option, the default storage engine
 is used. Normally, this is `MyISAM', but you can change it by using the
 -default-storage-engine or -default-table-type server startup option,
 or by setting the `storage_engine' or `table_type' system variable.
 
 When MySQL is installed on Windows using the MySQL Configuration
 Wizard, the `InnoDB' storage engine can be selected as the default
 instead of `MyISAM'. See  mysql-config-wizard-database-usage.
 
 To convert a table from one type to another, use an `ALTER TABLE'
 statement that indicates the new type:
 
      ALTER TABLE t ENGINE = MYISAM;
      ALTER TABLE t TYPE = BDB;
 
 See  create-table, and  alter-table.
 
 If you try to use a storage engine that is not compiled in or that is
 compiled in but deactivated, MySQL instead creates a table using the
 default storage engine, usually `MyISAM'. This behavior is convenient
 when you want to copy tables between MySQL servers that support
 different storage engines. (For example, in a replication setup,
 perhaps your master server supports transactional storage engines for
 increased safety, but the slave servers use only non-transactional
 storage engines for greater speed.)
 
 This automatic substitution of the default storage engine for
 unavailable engines can be confusing for new MySQL users. A warning is
 generated whenever a storage engine is automatically changed.
 
 For new tables, MySQL always creates an `.frm' file to hold the table
 and column definitions. The table's index and data may be stored in one
 or more other files, depending on the storage engine. The server
 creates the `.frm' file above the storage engine level. Individual
 storage engines create any additional files required for the tables
 that they manage.
 
 A database may contain tables of different types. That is, tables need
 not all be created with the same storage engine.
 
 Transaction-safe tables (TSTs) have several advantages over
 non-transaction-safe tables (NTSTs):
 
    * They are safer. Even if MySQL crashes or you get hardware
      problems, you can get your data back, either by automatic recovery
      or from a backup plus the transaction log.
 
    * You can combine many statements and accept them all at the same
      time with the `COMMIT' statement (if autocommit is disabled).
 
    * You can execute `ROLLBACK' to ignore your changes (if autocommit
      is disabled).
 
    * If an update fails, all of your changes are reverted. (With
      non-transaction-safe tables, all changes that have taken place are
      permanent.)
 
    * Transaction-safe storage engines can provide better concurrency
      for tables that get many updates concurrently with reads.
 
 You can combine transaction-safe and non-transaction-safe tables in the
 same statements to get the best of both worlds. However, although MySQL
 supports several transaction-safe storage engines, for best results,
 you should not mix different storage engines within a transaction with
 autocommit disabled. For example, if you do this, changes to
 non-transaction-safe tables still are committed immediately and cannot
 be rolled back. For information about this and other problems that can
 occur in transactions that use mixed storage engines, see 
 commit.
 
 Non-transaction-safe tables have several advantages of their own, all
 of which occur because there is no transaction overhead:
 
    * Much faster
 
    * Lower disk space requirements
 
    * Less memory required to perform updates

Info Catalog

(mysql.info) sql-syntax

(mysql.info) Top

(mysql.info) ndbcluster

automatically generated byinfo2html

(mysql.info) storage-engines

Menu