Oracle 5.0, Reference Manual

Page: 700 / 3232

Optimizing
SELECT
Statements
680
Because
DISTINCT
may use
GROUP BY
, you should be aware of how MySQL works with columns in
ORDER BY
or
HAVING
clauses that are not part of the selected columns. See Section 12.15.3, “MySQL
Extensions to
GROUP BY
” .
In most cases, a
DISTINCT
clause can be considered as a special case of
GROUP BY
. For example,
the following two queries are equivalent:
SELECT DISTINCT c1, c2, c3 FROM t1
WHERE c1 > const ;
SELECT c1, c2, c3 FROM t1
WHERE c1 > const GROUP BY c1, c2, c3;
Due to this equivalence, the optimizations applicable to
GROUP BY
queries can be also applied to
queries with a
DISTINCT
clause. Thus, for more details on the optimization possibilities for
DISTINCT
queries, see Section 8.3.1.12, “
GROUP BY
Optimization” .
When combining
LIMIT row_count
with
DISTINCT
, MySQL stops as soon as it finds
row_count
unique rows.
If you do not use columns from all tables named in a query, MySQL stops scanning any unused tables
as soon as it finds the first match. In the following case, assuming that
t1
is used before
t2
(which you
can check with
EXPLAIN
), MySQL stops reading from
t2
(for any particular row in
t1
) when it finds the
first row in
t2
:
SELECT DISTINCT t1.a FROM t1, t2 where t1.a=t2.a;
8.3.1.14. Optimizing Subqueries with
EXISTS
Strategy
Certain optimizations are applicable to comparisons that use the
IN
operator to test subquery results
(or that use
=ANY
, which is equivalent). This section discusses these optimizations, particularly with
regard to the challenges that
NULL
values present. Suggestions on what you can do to help the
optimizer are given at the end of the discussion.
Consider the following subquery comparison:
outer_expr IN (SELECT inner_expr FROM ... WHERE subquery_where )
MySQL evaluates queries “from outside to inside.” That is, it first obtains the value of the outer
expression
outer_expr
, and then runs the subquery and captures the rows that it produces.
A very useful optimization is to “inform” the subquery that the only rows of interest are those where the
inner expression
inner_expr
is equal to
outer_expr
. This is done by pushing down an appropriate
equality into the subquery's
WHERE
clause. That is, the comparison is converted to this:
EXISTS (SELECT 1 FROM ... WHERE subquery_where AND outer_expr = inner_expr )
After the conversion, MySQL can use the pushed-down equality to limit the number of rows that it must
examine when evaluating the subquery.
More generally, a comparison of
N
values to a subquery that returns
N
-value rows is subject to the
same conversion. If
oe_i
and
ie_i
represent corresponding outer and inner expression values, this
subquery comparison:
( oe_1 , ..., oe_N ) IN
(SELECT ie_1 , ..., ie_N FROM ... WHERE subquery_where )
Becomes:
EXISTS (SELECT 1 FROM ... WHERE subquery_where
AND oe_1 = ie_1
AND ...
AND oe_N = ie_N )
The following discussion assumes a single pair of outer and inner expression values for simplicity.