Table types in MySQL: Part 1 – HEAP tables

The MySQL table types

We all know accessing data in RAM is a lot quicker than accessing data on disk. So, with this in mind, wouldn’t it make sense for MySQL to store as much data as possible in memory? The answer is of course yes, and MySQL can in a number of ways. Query caches, holding indexes in memory, and most extreme of all, holding an entire table in memory. The latter is the topic of this article. Tables in memory are called HEAP tables. The MySQL table types include:

• MyISAM tables, the default table type, which employs table locking, making it ideal for high volume select, low volume insert/update environments, such as most websites.
• InnoDB tables, which supports transactions and referential integrity
• ISAM tables, the old MySQL table type, replaced by MyISAM in most instances now.
• BDB, which are also transaction safe.
• MERGE tables, a table type for a collection of MyISAM table types, allowing them to be used as one.

Creating a HEAP table

Creating a HEAP table is simply a matter of specifying the table type as HEAP, for example as follows:

mysql> CREATE TABLE heapofdust (id INT, fname VARCHAR (40)) TYPE=HEAP;
Query OK, 0 rows affected (0.08 sec)

Note that HEAP tables cannot contain fields of type TEXT or BLOB. If you try to create a HEAP table containing one of these types, you will get an error, as follows:

mysql> CREATE TABLE heapofleaves (id INT, fname VARCHAR (40), description TEXT) TYPE=HEAP;
ERROR 1163: The used table type doesn't support BLOB/TEXT columns

Syntax-wise, you can now INSERT and SELECT data as you would for an ordinary MyISAM table, just faster. In early 2002, John Lim wrote some benchmarks to measure the difference HEAP tables make. He found that HEAP tables were faster than MyISAM tables, but not by much. It is likely that with MySQL 4’s table cache, the differences will be even less. These were his results:

Table type	100 000 inserts (seconds)	Inserts per s	50 000 selects (s)	Selects/s
MySQL 3.23 Heap (Mem used: 10Mb)	23.82	4,198	21.96	2,276
MySQL 3.23 MyISAM	33.42	2,992	25.91	1,930

You can find the full details of his benchmarks (which also compare other DBMS’) at the PHP Everywhere site.

Of course, benchmarks mean little if they aren not relevant to your setup. Systems with slow disks may benefit more from a move to HEAP. Most important to consider is whether you have enough memory to hold the entire table in memory. If at any point you run out, the table will automatically be converted to MyISAM, although this may come at an inconvenient time, and you will want to avoid it. There are a number of ways to manage memory usage and HEAP tables. First, the MySQL variable, max_heap_table_size sets the maximum size a HEAP table can be (before converting it to MyISAM). This variable is set in the my.cnf (or my.ini) file. You can read more about setting the MySQL variables in the article Optimizing MySQL: Hardware and the Mysqld Variables.

The alternative (you can use both methods together) is to specify the MAX_ROWS attribute when creating the table.

mysql> CREATE TABLE heapofleaves (id INT, fname VARCHAR (40)) TYPE=HEAP MAX_ROWS=1;

HEAP tables and indexes

Heap tables have a number of limitations when it comes to the use of indexes. Let’s take a look at this behavior, and compare it with an identical MyISAM table. First, we will create two identical tables, one HEAP, the other MyISAM:

mysql> CREATE TABLE myisam1 (id INT, name VARCHAR(40) NOT NULL, INDEX(name)) TYPE MYISAM;

mysql> CREATE TABLE heap1 (id INT, name VARCHAR(40) NOT NULL, INDEX(name)) TYPE=HEAP;

mysql> INSERT INTO myisam1 VALUES(1,'ian'),(2,'Mzwake'),(3,'Helena');

mysql> INSERT INTO heap1 VALUES(1,'ian'),(2,'Mzwake'),(3,'Helena');

Now we will perform a simple query, returning a specific record. We will use EXPLAIN to see how the indexes are used (see here for more details on using EXPLAIN, and the index types).

mysql> EXPLAIN SELECT * FROM heap1 WHERE name='ian';
+-------+------+---------------+------+---------+-------+------+------------+
| table | type | possible_keys | key  | key_len | ref   | rows | Extra      |
+-------+------+---------------+------+---------+-------+------+------------+
| heap1 | ref  | name          | name |      40 | const |   10 | where used |
+-------+------+---------------+------+---------+-------+------+------------+

mysql> EXPLAIN SELECT * FROM myisam1 WHERE name='ian';
+---------+------+---------------+------+---------+-------+------+------------+
| table   | type | possible_keys | key  | key_len | ref   | rows | Extra      |
+---------+------+---------------+------+---------+-------+------+------------+
| myisam1 | ref  | name          | name |      40 | const |    1 | where used |
+---------+------+---------------+------+---------+-------+------+------------+