Optimizing
SELECT
Statements
667
For the example join described previously for the NLJ algorithm (without buffering), the join is done as
follow using join buffering:
for each row in t1 matching range {
for each row in t2 matching reference key {
store used columns from t1, t2 in join buffer
if buffer is full {
for each row in t3 {
for each t1, t2 combination in join buffer {
if row satisfies join conditions,
send to client
}
}
empty buffer
}
}
}
if buffer is not empty {
for each row in t3 {
for each t1, t2 combination in join buffer {
if row satisfies join conditions,
send to client
}
}
}
If
S
is the size of each stored
t1
,
t2
combination is the join buffer and
C
is the number of combinations
in the buffer, the number of times table
t3
is scanned is:
(
S
*
C
)/join_buffe 1
The number of
t3
scans decreases as the value of
join_buffer_size
[457]
increases, up to the
point when
join_buffer_size
[457]
is large enough to hold all previous row combinations. At that
point, there is no speed to be gained by making it larger.
8.3.1.9. Nested Join Optimization
As of MySQL 5.0.1, the syntax for expressing joins permits nested joins. The following discussion
refers to the join syntax described in
Section 13.2.8.2, “
JOIN
Syntax”
.
The syntax of
table_factor
is extended in comparison with the SQL Standard. The latter accepts
only
table_reference
, not a list of them inside a pair of parentheses. This is a conservative
extension if we consider each comma in a list of
table_reference
items as equivalent to an inner
join. For example:
SELECT * FROM t1 LEFT JOIN (t2, t3, t4)
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
is equivalent to:
SELECT * FROM t1 LEFT JOIN (t2 CROSS JOIN t3 CROSS JOIN t4)
ON (t2.a=t1.a AND t3.b=t1.b AND t4.c=t1.c)
In MySQL,
CROSS JOIN
is a syntactic equivalent to
INNER JOIN
(they can replace each other). In
standard SQL, they are not equivalent.
INNER JOIN
is used with an
ON
clause;
CROSS JOIN
is used
otherwise.
In versions of MySQL prior to 5.0.1, parentheses in
table_references
were just omitted and all
join operations were grouped to the left. In general, parentheses can be ignored in join expressions
containing only inner join operations.
After removing parentheses and grouping operations to the left, the join expression:
t1 LEFT JOIN (t2 LEFT JOIN t3 ON t2.b=t3.b OR t2.b IS NULL)
Содержание 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 ...