Dyntex.net Support Centre: MySQL Reference Manual p.505

<< previous page -- table of contents -- next page >>

Chapter_1

Chapter_2

Chapter_3

Chapter_4

Chapter_5

Return to Table of Contents

Table of Contents
1. General Information
2. MySQL Installation
3. Tutorial Introduction
4. Database Administration
5. MySQL Optimisation
6. MySQL Language Reference
7. MySQL Table Types
8. MySQL APIs
9. Extending MySQL

Chapter 7: MySQL Table Types 505 7.4 HEAP 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 over ow 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 prex of the key can be used to nd rows. HEAP tables use a xed 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 over ow 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 nd 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 aect 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.

MySQL Reference Manual

Our Partners

SpiritHost - web hosting for spiritual and education sites
SpiritHit.com - Religious and Spiritual Portal

Return to Table of Contents	Back to top

Web Hosting: Manuals & FAQ's

1. Unix-Based Web Hosting
2. Unix Dedicated Servers
3. Windows Dedicated Servers
4. CuteFTP User’s Guide
5. CuteHTML User’s Guide
6. WS_FTP Pro User's Guide
7. Miva Order User's Guide
8. Miva Merchant User's Guide