(mysql.info.gz) Storage engines

 
 14 MySQL Storage Engines and Table Types
 ****************************************
 
 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:
 
    * The original storage engine was `ISAM', which managed
      non-transactional tables. This engine has been replaced by
      `MyISAM' and should no longer be used. It is deprecated in MySQL
      4.1, and will be removed in MySQL 5.0.
 
    * In MySQL 3.23.0, the `MyISAM' and `HEAP' storage engines were
      introduced.  `MyISAM' is an improved replacement for `ISAM'.  The
      `HEAP' storage engine provides in-memory tables.  The `MERGE'
      storage engine was added in MySQL 3.23.25.  It allows a collection
      of identical `MyISAM' tables to be handled as a single table.  All
      three of these storage engines handle non-transactional tables,
      and all are included in MySQL by default.  Note that the `HEAP'
      storage engine has been renamed the `MEMORY' engine.
 
    * The `InnoDB' and `BDB' storage engines that handle
      transaction-safe tables were introduced in later versions of MySQL
      3.23.  Both are available in source distributions as of MySQL
      3.23.34a.  `BDB' is included in MySQL-Max binary distributions on
      those operating systems that support it.  `InnoDB' also is
      included in MySQL-Max binary distributions for MySQL 3.23.
      Beginning with MySQL 4.0, `InnoDB' is included by default in all
      MySQL binary distributions.  In source distributions, you can
      enable or disable either engine by configuring MySQL as you like.
 
    * The `EXAMPLE' storage engine was added in MySQL 4.1.3.  It is a
      "stub" engine that does nothing.  You can create tables with this
      engine, but no data can be stored into 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 source code distributions as of MySQL 4.1.2 and
      binary distributions as of MySQL-Max 4.1.3.
 
    * The `ARCHIVE' storage engine was added in MySQL 4.1.3.  It is used
      for storing large amounts of data without indexes in a very small
      footprint.
 
    * The `CSV' storage engine was added in MySQL 4.1.4. This engine
      stores data in text files using comma-separated-values format.
 
    * The `FEDERATED' storage engine was added in MySQL 5.0.3. This
      engine stores data in a remote database. In this release, it works
      with MySQL only, using the MySQL C Client API. Future releases
      will be able to connect to other data sources using other driver
      or client connection methods.
 
 
 This chapter describes each of the MySQL storage engines except for
 `InnoDB' and `NDB Cluster', which are covered in  InnoDB and
  NDBCluster.
 
 When you create a new table, you can tell MySQL what type of table to
 create 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;
 
 `ENGINE' is the preferred term, but cannot be used before MySQL 4.0.18.
 `TYPE' is available beginning with MySQL 3.23.0, the first version of
 MySQL for which multiple storage engines were available.
 
 If you omit the `ENGINE' or `TYPE' option, the default storage engine
 is used.  By default this is `MyISAM'.  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 will be the default instead of `MyISAM'.
  mysql-config-wizard-introduction.
 
 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' CREATE TABLE. and  `ALTER TABLE' 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 of type
 `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 `MyISAM' table type when an
 unavailable type is specified can be confusing for new MySQL users.  In
 MySQL 4.1 and up, a warning is generated when a table type is
 automatically changed.
 
 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 table type.  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.
 
 Transaction-safe tables (TSTs) have several advantages over
 non-transaction-safe tables (NTSTs):
 
    * 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 your changes will be restored. (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.
 
 Note that to use the `InnoDB' storage engine in MySQL 3.23, you must
 configure at least the `innodb_data_file_path' startup option.  In 4.0
 and up, `InnoDB' uses default configuration values if you specify none.
  `InnoDB' configuration InnoDB configuration.
 
 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
 
 You can combine transaction-safe and non-transaction-safe tables in the
 same statements to get the best of both worlds. However, within a
 transaction with autocommit disabled, changes to non-transaction-safe
 tables still are committed immediately and cannot be rolled back.

 
* MyISAM storage engine       The `MyISAM' 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
* ISAM storage engine         The `ISAM' Storage Engine

Info Catalog

(mysql.info.gz) SQL Syntax

(mysql.info.gz) Top

(mysql.info.gz) InnoDB

automatically generated byinfo2html

(mysql.info.gz) Storage engines

Menu