`HEAP' Tables
=============

`HEAP' tables use hashed indexes and are stored in memory.  This makes
them very fast, but if MySQL crashes you will lose all data stored in
them.  `HEAP' is very useful for temporary tables!


is common with hashed tables:

     mysql> CREATE TABLE test TYPE=HEAP SELECT ip,SUM(downloads) AS down
         ->                   FROM log_table GROUP BY ip;
     mysql> SELECT COUNT(ip),AVG(down) FROM test;
     mysql> DROP TABLE test;

Here are some things you should consider when you use `HEAP' tables:

   * You should always use specify `MAX_ROWS' in the `CREATE' statement
     to ensure that you accidentally do not use all memory.

   * Indexes will only be used with `=' and `<=>' (but are VERY fast).

   * `HEAP' tables can only use whole keys to search for a row; compare
     this to `MyISAM' tables where any prefix of the key can be used to
     find rows.

   * `HEAP' tables use a fixed record length format.

   * `HEAP' doesn't support `BLOB'/`TEXT' columns.

   * `HEAP' doesn't support `AUTO_INCREMENT' columns.

   * Prior to MySQL 4.0.2, `HEAP' doesn't support an index on a `NULL'
     column.

   * You can have non-unique keys in a `HEAP' table (this isn't common
     for hashed tables).

   * `HEAP' tables are shared between all clients (just like any other
     table).

   * You can't search for the next entry in order (that is, to use the
     index to do an `ORDER BY').

    and are
     reused when you insert new data into the table.

   * You need enough extra memory for all `HEAP' tables that you want
     to use at the same time.

   * To free memory, you should execute `DELETE FROM heap_table',
     `TRUNCATE heap_table' or `DROP TABLE heap_table'.

   * MySQL cannot find out approximately how many rows there are
     between two values (this is used by the range optimiser to decide
     which index to use).  This may affect some queries if you change a
     `MyISAM' table to a `HEAP' table.

   * To ensure that you accidentally don't do anything foolish, you
     can't create `HEAP' tables bigger than `max_heap_table_size'.

The memory needed for one row in a `HEAP' table is:

     SUM_OVER_ALL_KEYS(max_length_of_key + sizeof(char*) * 2)
     + ALIGN(length_of_row+1, sizeof(char*))

`sizeof(char*)' is 4 on 32-bit machines and 8 on 64-bit machines.

[Назад] [Содержание] [Вперед]