38.6. MEMORY-Specific Optimizations

38.6. MEMORY-Specific Optimizations

The MEMORY storage engine keeps table contents in memory, which makes table access very fast. MEMORY thus is a good choice for frequently accessed lookup tables.

The MEMORY engine supports HASH and BTREE indexing methods. Choose the method that is most appropriate to the type of lookups you'll be performing. For information on the characteristics of these methods, see Section 8.6.3, "Choosing an Indexing Algorithm."

Take care not to create MEMORY tables that become very large, or an excessive number of MEMORY tables. The combined size of these tables amounts to memory that is unavailable for other purposes. To prevent runaway memory use, set the max_heap_table_size system variable; an error will occur if you try to make a MEMORY table larger than this size. (However, the limit imposed by this variable is per-table, not global, so it is still possible to use lots of memory by creating many MEMORY tables.)

To free memory used by MEMORY tables, you should drop or truncate them when you no longer need their contents.