(mysql.info) memory-storage-engine

 
 14.4 The `MEMORY' (`HEAP') Storage Engine
 =========================================
 
 The `MEMORY' storage engine creates tables with contents that are
 stored in memory. Formerly, these were known as `HEAP' tables. `MEMORY'
 is the preferred term, although `HEAP' remains supported for backward
 compatibility.
 
 Each `MEMORY' table is associated with one disk file. The filename
 begins with the table name and has an extension of `.frm' to indicate
 that it stores the table definition.
 
 To specify explicitly that you want to create a `MEMORY' table,
 indicate that with an `ENGINE' table option:
 
      CREATE TABLE t (i INT) ENGINE = 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.
 
 As indicated by the name, `MEMORY' tables are stored in memory. They
 use hash indexes by default, which makes them very fast, and very
 useful for creating temporary tables.  However, when the server shuts
 down, all rows stored in `MEMORY' tables are lost. The tables themselves
 continue to exist because their definitions are stored in `.frm' files
 on disk, but they are empty when the server restarts.
 
 This example shows how you might create, use, and remove a `MEMORY'
 table:
 
      mysql> CREATE TABLE test ENGINE=MEMORY
          ->     SELECT ip,SUM(downloads) AS down
          ->     FROM log_table GROUP BY ip;
      mysql> SELECT COUNT(ip),AVG(down) FROM test;
      mysql> DROP TABLE test;
 
 `MEMORY' tables have the following characteristics:
 
    * Space for `MEMORY' tables is allocated in small blocks. Tables use
      100% dynamic hashing for inserts. No overflow area or extra key
      space is needed. No extra space is needed for free lists. Deleted
      rows are put in a linked list and are reused when you insert new
      data into the table.  `MEMORY' tables also have none of the
      problems commonly associated with deletes plus inserts in hashed
      tables.
 
    * `MEMORY' tables can have up to 32 indexes per table, 16 columns
      per index and a maximum key length of 500 bytes.
 
    * The `MEMORY' storage engine implements both `HASH' and `BTREE'
      indexes.  You can specify one or the other for a given index by
      adding a `USING' clause as shown here:
 
           CREATE TABLE lookup
               (id INT, INDEX USING HASH (id))
               ENGINE = MEMORY;
           CREATE TABLE lookup
               (id INT, INDEX USING BTREE (id))
               ENGINE = MEMORY;
 
      General characteristics of B-tree and hash indexes are described
      in  mysql-indexes.
 
    * You can have non-unique keys in a `MEMORY' table. (This is an
      uncommon feature for implementations of hash indexes.)
 
    * If you have a hash index on a `MEMORY' table that has a high
      degree of key duplication (many index entries containing the same
      value), updates to the table that affect key values and all
      deletes are significantly slower. The degree of this slowdown is
      proportional to the degree of duplication (or, inversely
      proportional to the index cardinality). You can use a `BTREE'
      index to avoid this problem.
 
    * Columns that are indexed can contain `NULL' values.
 
    * `MEMORY' tables use a fixed-length row storage format.
 
    * `MEMORY' tables cannot contain `BLOB' or `TEXT' columns.
 
    * `MEMORY' includes support for `AUTO_INCREMENT' columns.
 
    * You can use `INSERT DELAYED' with `MEMORY' tables. See 
      insert-delayed.
 
    * `MEMORY' tables are shared among all clients (just like any other
      non-`TEMPORARY' table).
 
    * `MEMORY' table contents are stored in memory, which is a property
      that `MEMORY' tables share with internal tables that the server
      creates on the fly while processing queries. However, the two
      types of tables differ in that `MEMORY' tables are not subject to
      storage conversion, whereas internal tables are:
 
         * If an internal table becomes too large, the server
           automatically converts it to an on-disk table. The size limit
           is determined by the value of the `tmp_table_size' system
           variable.
 
         * `MEMORY' tables are never converted to disk tables. To ensure
           that you don't accidentally do anything foolish, you can set
           the `max_heap_table_size' system variable to impose a maximum
           size on `MEMORY' tables.  For individual tables, you can also
           specify a `MAX_ROWS' table option in the `CREATE TABLE'
           statement.
 
    * The server needs sufficient memory to maintain all `MEMORY' tables
      that are in use at the same time.
 
    * To free memory used by a `MEMORY' table when you no longer require
      its contents, you should execute `DELETE' or `TRUNCATE TABLE', or
      remove the table altogether using `DROP TABLE'.
 
    * If you want to populate a `MEMORY' table when the MySQL server
      starts, you can use the -init-file option. For example, you can put
      statements such as `INSERT INTO ... SELECT' or `LOAD DATA INFILE'
      into this file to load the table from a persistent data source. See
       server-options, and  load-data.
 
    * If you are using replication, the master server's `MEMORY' tables
      become empty when it is shut down and restarted. However, a slave
      is not aware that these tables have become empty, so it returns
      out-of-date content if you select data from them. When a `MEMORY'
      table is used on the master for the first time since the master
      was started, a `DELETE' statement is written to the master's
      binary log automatically, thus synchronizing the slave to the
      master again. Note that even with this strategy, the slave still
      has outdated data in the table during the interval between the
      master's restart and its first use of the table. However, if you
      use the -init-file option to populate the `MEMORY' table on the
      master at startup, it ensures that this time interval is zero.
 
    * The memory needed for one row in a `MEMORY' table is calculated
      using the following expression:
 
           SUM_OVER_ALL_BTREE_KEYS(MAX_LENGTH_OF_KEY + sizeof(char*) × 4)
           + SUM_OVER_ALL_HASH_KEYS(sizeof(char*) × 2)
           + ALIGN(LENGTH_OF_ROW+1, sizeof(char*))
 
      `ALIGN()' represents a round-up factor to cause the row length to
      be an exact multiple of the `char' pointer size.  `sizeof(char*)'
      is 4 on 32-bit machines and 8 on 64-bit machines.
 
 *Additional resources*
 
    * A forum dedicated to the `MEMORY' storage engine is available at
      `http://forums.mysql.com/list.php?92'.
Info Catalog
(mysql.info) merge-storage-engine
(mysql.info) storage-engines
(mysql.info) bdb-storage-engine
automatically generated byinfo2html