Extended Keys
Syntax
Enable:
Disable:
Description
Extended Keys is an optimization set with the optimizer_switch system variable, which makes use of existing components of InnoDB keys to generate more efficient execution plans. Using these components in many cases allows the server to generate execution plans which employ index-only look-ups. It is set by default.
Extended keys can be used with:
ref and eq-ref accesses
range scans
index-merge scans
loose scans
min/max optimizations
Examples
An example of how extended keys could be employed for a query built over a DBT-3/TPC-H database with one added index
defined on p_retailprice
:
The above query asks for the orderkeys
of the orders placed on 1992-07-01
which contain parts with a retail price greater than $2095.
Using Extended Keys, the query could be executed by the following execution plan:
Scan the entries of the index
i_p_retailprice
wherep_retailprice>2095
and readp_partkey
values from the extended keys.For each value
p_partkey
make an index look-up into the table lineitem employing indexi_l_partkey
and fetch the values ofl_orderkey
from the extended index.For each fetched value of
l_orderkey
, append it to the date'1992-07-01'
and use the resulting key for an index look-up by indexi_o_orderdate
to fetch the values ofo_orderkey
from the found index entries.
All access methods of this plan do not touch table rows, which results in much better performance.
Here is the explain output for the above query:
See Also
This page is licensed: CC BY-SA / Gnu FDL
Last updated
Was this helpful?