HANDLER for MEMORY Tables

This article explains how to use HANDLER commands efficiently with MEMORY/HEAP tables.

If you want to scan a table for different key values, not just search for exact key values, you should create your keys with USING BTREE:

CREATE TABLE t1 (a INT, b INT, KEY(a), KEY b USING BTREE (b)) ENGINE=memory;

In the above table, a is a HASH key that only supports exact matches (=) while b is a BTREE key that you can use to scan the table in key order, starting from start or from a given key value.

The limitations for HANDLER READ with MEMORY|HEAP tables are:

Limitations for HASH keys

• You must use all key parts when searching for a row.

• You can't do a key scan of all values. You can only find all rows with the same key value.

• READ NEXT gives an error 1031 if the tables changed since last read.

Limitations for BTREE keys

• READ NEXT gives an error 1031 if the tables changed since last read. This limitation can be lifted in the future.

Limitations for table scans

• READ NEXT gives an error 1031 if the table was truncated since last READ call.

See also

See also the limitations listed in HANDLER commands.

_{This page is licensed: CC BY-SA / Gnu FDL}