As expected, the use of indexes is identical and efficient. However, when we alter the query to look for a range (all names greater than a specific name), note the differences between the two table types.
mysql> EXPLAIN SELECT * FROM myisam1 WHERE name>'ian';
+---------+-------+---------------+------+---------+------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+---------+-------+---------------+------+---------+------+------+------------+
| myisam1 | range | name | name | 40 | NULL | 2 | where used |
+---------+-------+---------------+------+---------+------+------+------------+
mysql> EXPLAIN SELECT * FROM heap1 WHERE name>'ian';
+-------+------+---------------+------+---------+------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+------+---------+------+------+------------+
| heap1 | ALL | name | NULL | NULL | NULL | 3 | where used |
+-------+------+---------------+------+---------+------+------+------------+
The MyISAM table uses a ‘range’ join, while the HEAP table does an ‘ALL’ join, the worst type. HEAP tables only apply an index with the ‘=’ and ‘<=>’ operators. If your application relies heavily on range indexes, HEAP tables may even be slower than MyISAM tables. Let’s look at another important difference:
mysql> EXPLAIN SELECT * FROM myisam1 WHERE name LIKE 'ia%';
+---------+-------+---------------+------+---------+------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+---------+-------+---------------+------+---------+------+------+------------+
| myisam1 | range | name | name | 40 | NULL | 1 | where used |
+---------+-------+---------------+------+---------+------+------+------------+
mysql> EXPLAIN SELECT * FROM heap1 WHERE name LIKE 'ia%';
+-------+------+---------------+------+---------+------+------+------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+-------+------+---------------+------+---------+------+------+------------+
| heap1 | ALL | name | NULL | NULL | NULL | 3 | where used |
+-------+------+---------------+------+---------+------+------+------------+
HEAP tables can only use whole keys to return results, while MyISAM can use prefixes. In summary. here are the important differences:
- HEAP indexes cannot be built on BLOB or TEXT fields
- HEAP tables cannot use partial keys (prefixes)
- HEAP tables do not support AUTO_INCREMENT fields
- HEAP indexes can only use the ‘=’ and ‘<=>’ operators
- HEAP indexes cannot be used to return an ‘ORDER BY’ result.
- HEAP indexes cannot provide information on how many records are between two results, which would assist the optimizer in its choice of index.
The last three reasons are all related. HEAP tables use hashed indexes, which allow them to pinpoint specific matches extremely quickly, but do not return any kind of range data. An index matches a record, but there is no ordering to allow it to return subsequent records.
HEAP table usage
HEAP tables are automatically used by MySQL when creating temporary tables (unless they contain BLOB or TEXT fields). If the temporary table becomes too big (as determined by max_heap_table_size and tmp_table_size), the temporary table is automatically converted to MyISAM.
HEAP tables can also be used where the table size can fit into the available RAM, and where UPDATEs are infrequent. Since HEAP tables are stored in RAM, if the machine crashes or loses power, all data is lost. For this reason they’re not ideal for holding important changes, but are better suited to providing a subset of another table (which can then record the changes), making it available for fast access. The ideal scenario is one where updates occur to a MyISAM table once a day (or the changes only need to be accessible once a day) allowing a HEAP table to generated from this table for the day’s usage, providing speedy, disk-free access. For example, once a day the table heap2 is created from the table myisam1 (which can contain TEXT/BLOB fields):
mysql> CREATE TABLE heap2 TYPE=HEAP SELECT name FROM myisam1;
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM heap2;
+--------+
| name |
+--------+
| Helena |
| ian |
| Mzwake |
+--------+
3 rows in set (0.00 sec)
mysql> ALTER TABLE heap2 ADD INDEX(name);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> DESC heap2;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| name | char(40) | | MUL | | |
+-------+----------+------+-----+---------+-------+
The table heap2 now has an index (which can be different from that on the original MyiSAM table), and exists in memory, for lightning fast access. In the meantime, the MyISAM table can be continually updated, and not impact on the access speed of the HEAP table at all. These changes will become available to users for fast querying the next day. One small example, but hopefully it gives you some inspiration for using HEAP tables in your own environments. Good luck!
This article originally appeared on DatabaseJournal.com.
