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

If you want to scan a table for over 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 error 1031 if the tables changed since last read.

Limitations for BTREE keys

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

Limitations for table scans

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

See also

See also the the limitations listed in HANDLER commands.


Comments loading...
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.