Hi,

Can someone get me clear about HEAP/MEMORY Storage Engine available in MYSQL.

1. What are the advantages and disadvantages for using MEMORY Storage Engine?
2. In what situation we can define the Storage Engine as HEAP / MEMORY Storage Engine can be defined for a table?

__________________
-Murali..

hey,


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.

MEMORY tables have the following Advantage:

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;

hi,

The disadvantages of MERGE tables are:

You can use only identical MyISAM tables for a MERGE table.

You cannot use a number of MyISAM features in MERGE tables. For example, you cannot create FULLTEXT indexes on MERGE tables. (You can, of course, create FULLTEXT indexes on the underlying MyISAM tables, but you cannot search the MERGE table with a full-text search.)

If the MERGE table is non-temporary, all underlying MyISAM tables must be non-temporary, too. If the MERGE table is temporary, the MyISAM tables can be any mix of temporary and non-temporary.

MERGE tables use more file descriptors. If 10 clients are using a MERGE table that maps to 10 tables, the server uses (10 × 10) + 10 file descriptors. (10 data file descriptors for each of the 10 clients, and 10 index file descriptors shared among the clients.)

Key reads are slower.

__________________
H2O

Without us, no one can survive..