From: stefan
Date: December 16 2005 9:02pm
Subject: svn commit - mysqldoc@docsrva: r577 - trunk/refman-4.1
List-Archive: http://lists.mysql.com/commits/205
Message-Id: <200512162102.jBGL241R013644@docsrva.mysql.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Author: shinz
Date: 2005-12-16 22:02:03 +0100 (Fri, 16 Dec 2005)
New Revision: 577
Log:
Loose index scans appear in 5.0 only (Domas)
Modified:
trunk/refman-4.1/optimization.xml
Modified: trunk/refman-4.1/optimization.xml
===================================================================
--- trunk/refman-4.1/optimization.xml 2005-12-16 18:46:45 UTC (rev 576)
+++ trunk/refman-4.1/optimization.xml 2005-12-16 21:02:03 UTC (rev 577)
@@ -3409,137 +3409,6 @@
performs a range scan, and then groups the resulting tuples.
-
-
- &title-loose-index-scan;
-
-
- The most efficient way is when the index is used to directly
- retrieve the group fields. With this access method, MySQL uses
- the property of some index types (for example, B-Trees) that
- the keys are ordered. This property allows use of lookup
- groups in an index without having to consider all keys in the
- index that satisfy all WHERE conditions.
- Since this access method considers only a fraction of the keys
- in an index, it is called a loose index
- scan. When there is no WHERE
- clause, a loose index scan reads as many keys as the number of
- groups, which may be a much smaller number than that of all
- keys. If the WHERE clause contains range
- predicates (see the discussion in of
- the range join type), a loose index scan
- looks up the first key of each group that satisfies the range
- conditions, and again reads the least possible number of keys.
- This is possible under the following conditions:
-
-
-
-
-
-
- The query is over a single table.
-
-
-
-
-
- The GROUP BY includes the first
- consecutive parts of the index (if instead of
- GROUP BY, the query has a
- DISTINCT clause, then all distinct
- attributes refer to the beginning of the index).
-
-
-
-
-
- The only aggregate functions used (if any) are
- MIN() and MAX(), and
- all of them refer to the same column.
-
-
-
-
-
- Any other parts of the index than those from the
- GROUP BY referenced in the query must
- be constants (that is, they must be referenced in
- equalities with constants), except for the argument of
- MIN() or MAX()
- functions.
-
-
-
-
-
-
- The EXPLAIN output for such queries shows
- Using index for group-by in the
- Extra column.
-
-
-
- The following queries provide several examples that fall into
- this category, assuming there is an index idx(c1, c2,
- c3) on table t1(c1,c2,c3,c4):
-
-
-
-SELECT c1, c2 FROM t1 GROUP BY c1, c2;
-SELECT DISTINCT c1, c2 FROM t1;
-SELECT c1, MIN(c2) FROM t1 GROUP BY c1;
-SELECT c1, c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
-SELECT MAX(c3), MIN(c3), c1, c2 FROM t1 WHERE c2 > const GROUP BY c1, c2;
-SELECT c2 FROM t1 WHERE c1 < const GROUP BY c1, c2;
-SELECT c1, c2 FROM t1 WHERE c3 = const GROUP BY c1, c2;
-
-
-
- The following queries cannot be executed with this quick
- select method, for the reasons given:
-
-
-
-
-
-
- There are other aggregate functions than
- MIN() or MAX(), for
- example:
-
-
-
-SELECT c1, SUM(c2) FROM t1 GROUP BY c1;
-
-
-
-
-
- The fields in the GROUP BY clause do
- not refer to the beginning of the index, as shown here:
-
-
-
-SELECT c1,c2 FROM t1 GROUP BY c2, c3;
-
-
-
-
-
- The query refers to a part of a key that comes after the
- GROUP BY part, and for which there is
- no equality with a constant, an example being:
-
-
-
-SELECT c1,c3 FROM t1 GROUP BY c1, c2;
-
-
-
-
-
-
-