How to Use the HANDLER Statement in MySQL for Faster Data Access

The HANDLER statement in MySQL provides a low-level mechanism to directly access storage engine-level functionality, bypassing some of the overhead associated with regular SQL queries. It can be especially useful for traversing a database in high-performance scenarios.

HANDLER is available for MyISAM and InnoDB tables, and can be used as a faster alternative to the SELECT statement.

Syntax

The official syntax looks like this:

HANDLER tbl_name OPEN [ [AS] alias]

HANDLER tbl_name READ index_name { = | <= | >= | < | > } (value1,value2,...)
    [ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name READ index_name { FIRST | NEXT | PREV | LAST }
    [ WHERE where_condition ] [LIMIT ... ]
HANDLER tbl_name READ { FIRST | NEXT }
    [ WHERE where_condition ] [LIMIT ... ]

HANDLER tbl_name CLOSE

But what this essentially means is that we first open the table, then we read it, and then we close it.

Like this:

HANDLER ... OPEN: Opens a table for subsequent operations.
HANDLER ... READ ...: Reads rows from the open table based on various options. We can use this multiple times, with different options (in between opening and closing the table).
HANDLER ... CLOSE: Closes the table.

Examples

Let’s run through some simple examples in order to illustrate the basic usage of the HANDLER statement.

Setup: Create Table and Populate

First, we’ll create a sample table named products and populate it with sample data in order to demonstrate various HANDLER read operations.

-- Create table
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10, 2),
    stock INT,
    INDEX idx_category (category)
);

-- Insert data
INSERT INTO products (name, category, price, stock) VALUES
('Laptop', 'Electronics', 999.99, 50),
('Smartphone', 'Electronics', 799.99, 100),
('Desk Chair', 'Furniture', 89.99, 20),
('Bookshelf', 'Furniture', 129.99, 10),
('Monitor', 'Electronics', 199.99, 75),
('Sofa', 'Furniture', 499.99, 5),
('Headphones', 'Electronics', 59.99, 200);

Key Lookup with `HANDLER`

The key lookup allows us to directly access specific rows based on indexed keys. The syntax is:

HANDLER tbl_name READ index_name { = | <= | >= | < | > } (value1,value2,...)
    [ WHERE where_condition ] [LIMIT ... ]

Here’s an example:

-- Open the table
HANDLER products OPEN;

-- Perform a key lookup on the 'idx_category' index, finding the first row with 'Electronics'
HANDLER products READ idx_category = ('Electronics');

-- Close the table
HANDLER products CLOSE;

Output:

+----+--------+-------------+--------+-------+
| id | name   | category    | price  | stock |
+----+--------+-------------+--------+-------+
|  1 | Laptop | Electronics | 999.99 |    50 |
+----+--------+-------------+--------+-------+

In this example:

We open the products table.
We then use the READ command to retrieve rows where the idx_category is ‘Electronics’, which refers to the indexed category column.
The table is closed afterward.

Here’s an example that uses the WHERE clause:

HANDLER products OPEN;

HANDLER products READ idx_category = ('Electronics')
WHERE stock >= 100;

HANDLER products CLOSE;

Output:

+----+------------+-------------+--------+-------+
| id | name       | category    | price  | stock |
+----+------------+-------------+--------+-------+
|  2 | Smartphone | Electronics | 799.99 |   100 |
+----+------------+-------------+--------+-------+

We can also use the LIMIT clause:

HANDLER products OPEN;

HANDLER products READ idx_category = ('Electronics')
LIMIT 2;

HANDLER products CLOSE;

Output:

+----+------------+-------------+--------+-------+
| id | name       | category    | price  | stock |
+----+------------+-------------+--------+-------+
|  1 | Laptop     | Electronics | 999.99 |    50 |
|  2 | Smartphone | Electronics | 799.99 |   100 |
+----+------------+-------------+--------+-------+

When we use the LIMIT clause, this returns as many rows up to the specified limit. So this enables us to return multiple rows at once.

We don’t need to open and close the table every time. We can open it once, run the various operations, and then close it. So we could do all the above operations like this:

HANDLER products OPEN;

HANDLER products READ idx_category = ('Electronics');

HANDLER products READ idx_category = ('Electronics')
WHERE stock >= 100;

HANDLER products READ idx_category = ('Electronics')
LIMIT 2;

HANDLER products CLOSE;

Key Scan with `HANDLER`

A key scan fetches rows in key order. The syntax goes like this:

HANDLER tbl_name READ index_name { FIRST | NEXT | PREV | LAST }
    [ WHERE where_condition ] [LIMIT ... ]

Let’s open the table and then run a bunch of HANDLER statements, each with different arguments:

HANDLER products OPEN;

HANDLER products READ idx_category FIRST;

HANDLER products READ idx_category NEXT;

HANDLER products READ idx_category NEXT LIMIT 10;

HANDLER products READ idx_category LAST
WHERE price > 100.00;

HANDLER products READ idx_category FIRST
WHERE price > 100.00;

HANDLER products READ idx_category FIRST
WHERE price > 100.00 LIMIT 10;

HANDLER products CLOSE;

Here’s the full output of all those statements in my MySQL client:

mysql> HANDLER products READ idx_category FIRST;
+----+--------+-------------+--------+-------+
| id | name   | category    | price  | stock |
+----+--------+-------------+--------+-------+
|  1 | Laptop | Electronics | 999.99 |    50 |
+----+--------+-------------+--------+-------+
1 row in set (0.00 sec)

mysql> 
mysql> HANDLER products READ idx_category NEXT;
+----+------------+-------------+--------+-------+
| id | name       | category    | price  | stock |
+----+------------+-------------+--------+-------+
|  2 | Smartphone | Electronics | 799.99 |   100 |
+----+------------+-------------+--------+-------+
1 row in set (0.00 sec)

mysql> 
mysql> HANDLER products READ idx_category NEXT LIMIT 10;
+----+------------+-------------+--------+-------+
| id | name       | category    | price  | stock |
+----+------------+-------------+--------+-------+
|  5 | Monitor    | Electronics | 199.99 |    75 |
|  7 | Headphones | Electronics |  59.99 |   200 |
|  3 | Desk Chair | Furniture   |  89.99 |    20 |
|  4 | Bookshelf  | Furniture   | 129.99 |    10 |
|  6 | Sofa       | Furniture   | 499.99 |     5 |
+----+------------+-------------+--------+-------+
5 rows in set (0.00 sec)

mysql> 
mysql> HANDLER products READ idx_category LAST
    -> WHERE price > 100.00;
+----+------+-----------+--------+-------+
| id | name | category  | price  | stock |
+----+------+-----------+--------+-------+
|  6 | Sofa | Furniture | 499.99 |     5 |
+----+------+-----------+--------+-------+
1 row in set (0.01 sec)

mysql> 
mysql> HANDLER products READ idx_category FIRST
    -> WHERE price > 100.00;
+----+--------+-------------+--------+-------+
| id | name   | category    | price  | stock |
+----+--------+-------------+--------+-------+
|  1 | Laptop | Electronics | 999.99 |    50 |
+----+--------+-------------+--------+-------+
1 row in set (0.00 sec)

mysql> 
mysql> HANDLER products READ idx_category FIRST
    -> WHERE price > 100.00 LIMIT 10;
+----+------------+-------------+--------+-------+
| id | name       | category    | price  | stock |
+----+------------+-------------+--------+-------+
|  1 | Laptop     | Electronics | 999.99 |    50 |
|  2 | Smartphone | Electronics | 799.99 |   100 |
|  5 | Monitor    | Electronics | 199.99 |    75 |
|  4 | Bookshelf  | Furniture   | 129.99 |    10 |
|  6 | Sofa       | Furniture   | 499.99 |     5 |
+----+------------+-------------+--------+-------+
5 rows in set (0.00 sec)

Table Scan with `HANDLER`

The syntax for a table scan goes like this:

HANDLER tbl_name READ { FIRST | NEXT }
    [ WHERE where_condition ] [LIMIT ... ]

So it’s a bit like the previous one, but instead of naming the index, we just use the table. Also we don’t have the LAST and PREVIOUS options.

Here are some examples:

HANDLER products OPEN;

HANDLER products READ FIRST;

HANDLER products READ NEXT;

HANDLER products READ FIRST
WHERE category = 'Furniture' LIMIT 10;

HANDLER products CLOSE;

Output:

+----+--------+-------------+--------+-------+
| id | name   | category    | price  | stock |
+----+--------+-------------+--------+-------+
|  1 | Laptop | Electronics | 999.99 |    50 |
+----+--------+-------------+--------+-------+

+----+------------+-------------+--------+-------+
| id | name       | category    | price  | stock |
+----+------------+-------------+--------+-------+
|  2 | Smartphone | Electronics | 799.99 |   100 |
+----+------------+-------------+--------+-------+

+----+------------+-----------+--------+-------+
| id | name       | category  | price  | stock |
+----+------------+-----------+--------+-------+
|  3 | Desk Chair | Furniture |  89.99 |    20 |
|  4 | Bookshelf  | Furniture | 129.99 |    10 |
|  6 | Sofa       | Furniture | 499.99 |     5 |
+----+------------+-----------+--------+-------+

Benefits of `HANDLER`

The MySQL documentation offers the following benefits of using HANDLER instead of a SELECT statement:

HANDLER is faster than SELECT:
- A designated storage engine handler object is allocated for the HANDLER ... OPEN. The object is reused for subsequent HANDLER statements for that table; it need not be reinitialized for each one.
- There is less parsing involved.
- There is no optimizer or query-checking overhead.
- The handler interface does not have to provide a consistent look of the data (for example, dirty reads are permitted), so the storage engine can use optimizations that SELECT does not normally permit.
HANDLER makes it easier to port to MySQL applications that use a low-level ISAM-like interface.
HANDLER enables you to traverse a database in a manner that is difficult (or even impossible) to accomplish with SELECT. The HANDLER interface is a more natural way to look at data when working with applications that provide an interactive user interface to the database.

Important Considerations

The HANDLER statement is only available for MyISAM and InnoDB tables.
It is primarily used for read operations. Inserts, updates, and deletes should still use standard SQL queries.
Once opened, the table object is not shared by other sessions and is not closed until the session calls HANDLER ... CLOSE or the session terminates.
If you open the table using an alias, further references to the open table with other HANDLER statements must use the alias rather than the table name.
If you don’t use an alias, but open the table using a table name qualified by the database name, further references must use the unqualified table name.

More Information

See the MySQL documentation for more information about the HANDLER statement.

Syntax

Examples

Setup: Create Table and Populate

Key Lookup with HANDLER

Key Scan with HANDLER

Table Scan with HANDLER

Benefits of HANDLER

Important Considerations

More Information

Key Lookup with `HANDLER`

Key Scan with `HANDLER`

Table Scan with `HANDLER`

Benefits of `HANDLER`