Copyright (C) 2000-2012 |
GNU Info (mysql.info)HEAPHEAP Tables =========== `HEAP' tables use a hashed index 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! The MySQL internal HEAP tables use 100% dynamic hashing without overflow areas. There is no extra space needed for free lists. `HEAP' tables also don't have problems with delete + inserts, which normally 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. * `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'). * Data for `HEAP' tables are allocated in small blocks. The tables are 100% dynamic (on inserting). No overflow areas and no extra key space are needed. Deleted rows are put in a linked list 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 optimizer 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. automatically generated by info2www version 1.2.2.9 |