Optimizing
SELECT
Statements
662
If the used indexes don't cover all columns used in the query, full rows are retrieved only when the
range conditions for all used keys are satisfied.
If one of the merged conditions is a condition over a primary key of an
InnoDB
or
BDB
table, it is not
used for row retrieval, but is used to filter out rows retrieved using other conditions.
8.3.1.4.2. The Index Merge Union Access Algorithm
The applicability criteria for this algorithm are similar to those for the Index Merge method intersection
algorithm. The algorithm can be employed when the table's
WHERE
clause was converted to several
range conditions on different keys combined with
OR
[881]
, and each condition is one of the following:
• In this form, where the index has exactly
N
parts (that is, all index parts are covered):
key_part1
=
const1
AND
key_part2
=
const2
... AND
key_partN
=
constN
• Any range condition over a primary key of an
InnoDB
or
BDB
table.
• A condition for which the Index Merge method intersection algorithm is applicable.
Examples:
SELECT * FROM t1 WHERE
key1
=1 OR
key2
=2 OR
key3
=3;
SELECT * FROM
innodb_table
WHERE (
key1
=1 AND
key2
=2) OR
(
key3
='foo' AND
key4
='bar') AND
key5
=5;
8.3.1.4.3. The Index Merge Sort-Union Access Algorithm
This access algorithm is employed when the
WHERE
clause was converted to several range conditions
combined by
OR
[881]
, but for which the Index Merge method union algorithm is not applicable.
Examples:
SELECT * FROM
tbl_name
WHERE
key_col1
< 10 OR
key_col2
< 20;
SELECT * FROM
tbl_name
WHERE (
key_col1
> 10 OR
key_col2
= 20) AND
nonkey_col
=30;
The difference between the sort-union algorithm and the union algorithm is that the sort-union algorithm
must first fetch row IDs for all rows and sort them before returning any rows.
8.3.1.5. Engine Condition Pushdown Optimization
This optimization improves the efficiency of direct comparisons between a nonindexed column and
a constant. In such cases, the condition is “pushed down” to the storage engine for evaluation. This
optimization can be used only by the
NDBCLUSTER
storage engine.
For MySQL Cluster, this optimization can eliminate the need to send nonmatching rows over the
network between the cluster's data nodes and the MySQL Server that issued the query, and can speed
up queries where it is used by a factor of 5 to 10 times over cases where condition pushdown could be
but is not used.
Suppose that a MySQL Cluster table is defined as follows:
CREATE TABLE t1 (
a INT,
b INT,
KEY(a)
) ENGINE=NDBCLUSTER;
Condition pushdown can be used with queries such as the one shown here, which includes a
comparison between a nonindexed column and a constant:
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...