6.4.2 HANDLER Syntax

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

The HANDLER statement provides direct access to the MyISAM table storage engine interface.

The first form of HANDLER statement opens a table, making it accessible via subsequent HANDLER ... READ statements. This table object is not shared by other threads and will not be closed until the thread calls HANDLER tbl_name CLOSE or the thread dies.

The second form fetches one row (or more, specified by LIMIT clause) where the index specified complies to the condition and WHERE condition is met. If the index consists of several parts (spans over several columns) the values are specified in comma-separated list, providing values only for few first columns is possible.

The third form fetches one row (or more, specified by LIMIT clause) from the table in index order, matching WHERE condition.

The fourth form (without index specification) fetches one row (or more, specified by LIMIT clause) from the table in natural row order (as stored in datafile) matching WHERE condition. It is faster than HANDLER tbl_name READ index_name when a full table scan is desired.

HANDLER ... CLOSE closes a table that was opened with HANDLER ... OPEN.

Note: If you're using HANDLER interface for PRIMARY KEY you should remember to quote the keyword PRIMARY with backticks: HANDLER tbl READ `PRIMARY` > (...)

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 is issued, table data can be modified (by this or any other thread) and these modifications may appear only partially in HANDLER ... NEXT or HANDLER ... PREV scans.

The reasons to use this interface instead of normal SQL are:

• It's faster than SELECT because:
  • A designated storage engine is allocated for the thread in HANDLER OPEN.
  • There is less parsing involved.
  • No optimiser and no query checking overhead.
  • The used table doesn't have to be locked between two handler requests.
  • The handler interface doesn't have to provide a consistent look of the data (for example, dirty-reads are allowed), so the storage engine can do optimisations that SQL doesn't normally allow.
• It makes it much easier to port applications that uses an ISAM like interface to MySQL.
• It allows one to traverse a database in a manner that is not easy (in some cases impossible) to do with SQL. The handler interface is more natural way to look at data when working with applications that provide an interactive user interfaces to the database.

User Comments

Add your own comment.