(mysql.info.gz) HANDLER

Info Catalog

(mysql.info.gz) DO

(mysql.info.gz) Data Manipulation

(mysql.info.gz) INSERT

 
 13.1.3 `HANDLER' Syntax
 -----------------------
 
      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' tables as MySQL 4.0.0 and
 `InnoDB' tables as of MySQL 4.0.3.
 
 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 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 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 ... INDEX_NAME = (col_a_val,col_b_val,col_c_val) ...
      HANDLER ... INDEX_NAME = (col_a_val,col_b_val) ...
      HANDLER ... INDEX_NAME = (col_a_val) ...
 
 The second `HANDLER ... READ' syntax fetches a row from the table in
 index order that matches `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.   `SELECT' SELECT.
 
 `HANDLER ... CLOSE' closes a table that was opened with `HANDLER ...
 OPEN'.
 
 Note: To use the `HANDLER' interface to refer to a table's `PRIMARY
 KEY', use the quoted identifier ``PRIMARY`':
 
      HANDLER TBL_NAME 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' statement is issued, table data can be
 modified (by this or any other thread) and these modifications might
 appear only partially in `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 the
           following `HANDLER' statements for the table; it need not be
           reinitialized for each one.
 
         * There is less parsing involved.
 
         * There is no optimizer or query-checking overhead.
 
         * The 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 use optimizations that `SELECT'
           doesn't normally allow.
 
 
    *  `HANDLER' makes it much easier to port applications that use an
      `ISAM'-like interface to MySQL.
 
    * `HANDLER' allows you to traverse a database in a manner that is not
      easy (or perhaps even impossible) to do 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.
 
 

Info Catalog

(mysql.info.gz) DO

(mysql.info.gz) Data Manipulation

(mysql.info.gz) INSERT