DOC HOME SITE MAP MAN PAGES GNU INFO SEARCH
 

(mysql.info.gz) MEMORY storage engine

Info Catalog (mysql.info.gz) MERGE storage engine (mysql.info.gz) Storage engines (mysql.info.gz) BDB storage engine
 
 14.3 The `MEMORY' (`HEAP') Storage Engine
 =========================================
 
 The `MEMORY' storage engine creates tables with contents that are stored
 in memory. Before MySQL 4.1, `MEMORY' tables are called `HEAP' tables.
 As of 4.1, `HEAP' is a synonym for `MEMORY', and `MEMORY' is the
 preferred term.
 
 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 a `MEMORY' table, indicate that with
 an `ENGINE' or `TYPE' table option:
 
      CREATE TABLE t (i INT) ENGINE = MEMORY;
      CREATE TABLE t (i INT) TYPE = HEAP;
 
 `MEMORY' tables are stored in memory and use hash indexes by default.
 This makes them very fast, and very useful for creating temporary
 tables.  However, when the server shuts down, all data stored in
 `MEMORY' tables is lost.  The tables continue to exist because their
 definitions are stored in the `.frm' files on disk, but their contents
 will be empty when the server restarts.
 
 Here is an example that shows how you might create, use, and remove a
 `MEMORY' table:
 
      mysql> CREATE TABLE test TYPE=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. The tables
      use 100% dynamic hashing (on inserting). No overflow areas and no
      extra key space are needed.  There is no extra space 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
      don't have problems with deletes plus inserts, which is common
      with hashed tables.
 
    * `MEMORY' tables allow up to 32 indexes per table, 16 columns per
      index, and a maximum key length of 500 bytes.
 
    * Before MySQL 4.1, the `MEMORY' storage engine implements only hash
      indexes.  From MySQL 4.1 on, hash indexes are still the default,
      but you can specify explicitly that a `MEMORY' table index should
      be `HASH' or `BTREE' by adding a `USING' clause:
 
           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 will be significantly slower.  The degree of 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.
 
    * `MEMORY' tables use a fixed record length format.
 
    * `MEMORY' doesn't support `BLOB' or `TEXT' columns.
 
    * `MEMORY' doesn't support `AUTO_INCREMENT' columns before MySQL
      4.1.0.
 
    * Prior to MySQL 4.0.2, `MEMORY' doesn't support indexes on columns
      that can contain `NULL' values.
 
    * `MEMORY' tables are shared between 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 enough extra memory to maintain all `MEMORY'
      tables that are in use at the same time.
 
    * To free memory used by a `MEMORY' table if you no longer require
      its contents, you should execute `DELETE' or `TRUNCATE TABLE', or
      else remove the table with `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 the file to load the table from some persistent data
      source.   Server options.
 
    * 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 will
      return out-of-date content if you select data from them.
      Beginning with MySQL 4.0.18, when a `MEMORY' table is used on the
      master for the first time since the master's startup, a `DELETE
      FROM' 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 out-of-date
      data in the table during the interval between the master's restart
      and its first use of the table.  But if you use the `--init-file'
      option to populate the `MEMORY' table on the master at startup, it
      ensures that the failing 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.
 
 
Info Catalog (mysql.info.gz) MERGE storage engine (mysql.info.gz) Storage engines (mysql.info.gz) BDB storage engine
automatically generated byinfo2html