Optimizing
SELECT
Statements
660
conditions are combined with
AND
[880]
, they form a condition that covers a set of rows contained
within the intersection of their intervals. For example, for this condition on a two-part index:
(
key_part1
= 1 AND
key_part2
< 2) OR (
key_part1
> 5)
The intervals are:
(1,-inf) < (
key_part1
,
key_part2
) < (1,2)
(5,-inf) < (
key_part1
,
key_part2
)
In this example, the interval on the first line uses one key part for the left bound and two key parts for
the right bound. The interval on the second line uses only one key part. The
key_len
column in the
EXPLAIN
output indicates the maximum length of the key prefix used.
In some cases,
key_len
may indicate that a key part was used, but that might be not what you
would expect. Suppose that
key_part1
and
key_part2
can be
NULL
. Then the
key_len
column
displays two key part lengths for the following condition:
key_part1
>= 1 AND
key_part2
< 2
But, in fact, the condition is converted to this:
key_part1
>= 1 AND
key_part2
IS NOT NULL
Section 8.3.1.3.1, “The Range Access Method for Single-Part Indexes”
, describes how optimizations
are performed to combine or eliminate intervals for range conditions on a single-part index. Analogous
steps are performed for range conditions on multiple-part indexes.
8.3.1.4. Index Merge Optimization
The Index Merge method is used to retrieve rows with several
range
[647]
scans and to merge
their results into one. The merge can produce unions, intersections, or unions-of-intersections of its
underlying scans. This access method merges index scans from a single table; it does not merge
scans across multiple tables.
Note
If you have upgraded from a previous version of MySQL, you should be
aware that this type of join optimization is first introduced in MySQL 5.0, and
represents a significant change in behavior with regard to indexes. (Formerly,
MySQL was able to use at most only one index for each referenced table.)
In
EXPLAIN
output, the Index Merge method appears as
index_merge
[647]
in the
type
column. In
this case, the
key
column contains a list of indexes used, and
key_len
contains a list of the longest
key parts for those indexes.
Examples:
SELECT * FROM
tbl_name
WHERE
key1
= 10 OR
key2
= 20;
SELECT * FROM
tbl_name
WHERE (
key1
= 10 OR
key2
= 20) AND
non_key
=30;
SELECT * FROM t1, t2
WHERE (t1.
key1
IN (1,2) OR t1.
key2
LIKE '
value
%')
AND t2.
key1
=t1.
some_col
;
SELECT * FROM t1, t2
WHERE t1.
key1
=1
AND (t2.
key1
=t1.
some_col
OR t2.
key2
=t1.
some_col2
);
The Index Merge method has several access algorithms (seen in the
Extra
field of
EXPLAIN
output):
•
Using intersect(...)
Summary of Contents for 5.0
Page 1: ...MySQL 5 0 Reference Manual ...
Page 18: ...xviii ...
Page 60: ...40 ...
Page 396: ...376 ...
Page 578: ...558 ...
Page 636: ...616 ...
Page 844: ...824 ...
Page 1234: ...1214 ...
Page 1427: ...MySQL Proxy Scripting 1407 ...
Page 1734: ...1714 ...
Page 1752: ...1732 ...
Page 1783: ...Configuring Connector ODBC 1763 ...
Page 1793: ...Connector ODBC Examples 1773 ...
Page 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Page 2850: ...2830 ...
Page 2854: ...2834 ...
Page 2928: ...2908 ...
Page 3000: ...2980 ...
Page 3122: ...3102 ...
Page 3126: ...3106 ...
Page 3174: ...3154 ...
Page 3232: ...3212 ...
