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
The HANDLER statement provides direct access
to table storage engine interfaces. It is available for
MyISAM and InnoDB tables.
The HANDLER ... OPEN statement opens a table,
making it accessible via subsequent HANDLER ...
READ statements. This table object is not shared by
other threads and is not closed until the thread calls
HANDLER ... CLOSE or the thread 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.
The first HANDLER ... READ syntax fetches a
row where the index specified satisfies the given values and the
WHERE condition is met. If you have a
multiple-column index, specify the index column values as a
comma-separated list. Either specify values for all the columns
in the index, or specify values for a leftmost prefix of the
index columns. Suppose that an index my_idx
includes three columns named col_a,
col_b, and col_c, in that
order. The HANDLER statement can specify
values for all three columns in the index, or for the columns in
a leftmost prefix. For example:
HANDLER ... READ my_idx = (col_a_val,col_b_val,col_c_val) ...
HANDLER ... READ my_idx = (col_a_val,col_b_val) ...
HANDLER ... READ my_idx = (col_a_val) ...
To employ the HANDLER interface to refer to a
table's PRIMARY KEY, use the quoted
identifier `PRIMARY`:
HANDLER tbl_name READ `PRIMARY` ...
The second HANDLER ... READ syntax fetches a
row from the table in index order that matches the
WHERE condition.
The third HANDLER ... READ syntax fetches a
row from the table in natural row order that matches the
WHERE condition. It is faster than
HANDLER tbl_name READ
index_name when a full
table scan is desired. Natural row order is the order in which
rows are stored in a MyISAM table data file.
This statement works for InnoDB tables as
well, but there is no such concept because there is no separate
data file.
Without a LIMIT clause, all forms of
HANDLER ... READ fetch a single row if one is
available. To return a specific number of rows, include a
LIMIT clause. It has the same syntax as for
the SELECT statement. See
Section 13.2.7, “SELECT Syntax”.
HANDLER ... CLOSE closes a table that was
opened with HANDLER ... OPEN.
HANDLER is a somewhat low-level statement.
For example, it does not provide consistency. That is,
HANDLER ... OPEN does
not take a snapshot of the table, and does
not lock the table. This means that after a
HANDLER ... OPEN statement is issued, table
data can be modified (by the current thread or other threads)
and these modifications might be only partially visible to
HANDLER ... NEXT or HANDLER ...
PREV scans.
There are several reasons to use the HANDLER
interface instead of normal SELECT
statements:
-
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 table does not have to be locked between two handler
requests.
The handler interface does not have to provide a
consistent look of the data (for example, dirty reads
are allowed), so the storage engine can use
optimizations that SELECT does not
normally allow.
For applications that use a low-level
ISAM-like interface,
HANDLER makes it much easier to port them
to MySQL.
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.
