# HANDLER for MEMORY Tables

This article explains how to use [HANDLER commands](https://mariadb.com/docs/server/reference/sql-structure/nosql/handler/handler-commands) efficiently with [MEMORY/HEAP](https://mariadb.com/docs/server/server-usage/storage-engines/memory-storage-engine) 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`:

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

In the above table, `a` is a [HASH](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/optimization-and-indexes/storage-engine-index-types#hash-indexes) key that only supports exact matches (=) while `b` is a [BTREE](https://mariadb.com/docs/server/ha-and-performance/optimization-and-tuning/optimization-and-indexes/storage-engine-index-types#b-tree-indexes) 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](https://mariadb.com/docs/server/reference/error-codes/mariadb-error-codes-1000-to-1099/e1031) 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](https://mariadb.com/docs/server/reference/sql-structure/nosql/handler/handler-commands).

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://mariadb.com/docs/server/reference/sql-structure/nosql/handler/handler-for-memory-tables.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.