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.

URL: https://mariadb.com/kb/en/handler-for-memory-tables/