From: Date: February 24 2006 5:45pm
Subject: svn commit - mysqldoc@docsrva: r1440 - in trunk: refman-5.1 refman-common
List-Archive: http://lists.mysql.com/commits/3118
Message-Id: <200602241645.k1OGj6qR025654@docsrva.mysql.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
Author: jstephens
Date: 2006-02-24 17:45:03 +0100 (Fri, 24 Feb 2006)
New Revision: 1440
Log:
trunk/refman-common/news-5.1.xml - Fix typo
trunk/refman-common/titles.en.ent - New section title
trunk/refman-5.1/sql-syntax.xml - Couple of fixes relating to partitioning
trunk/refman-5.1/partitioning.xml
- added note about partition keys and primary keys (Thanks, Jeb!)
- New section: Partition Pruning
Modified:
trunk/refman-5.1/partitioning.xml
trunk/refman-5.1/sql-syntax.xml
trunk/refman-common/news-5.1.xml
trunk/refman-common/titles.en.ent
Modified: trunk/refman-5.1/partitioning.xml
===================================================================
--- trunk/refman-5.1/partitioning.xml 2006-02-24 15:18:24 UTC (rev 1439)
+++ trunk/refman-5.1/partitioning.xml 2006-02-24 16:45:03 UTC (rev 1440)
@@ -363,7 +363,8 @@
queries that may not have been so when the partitioning scheme
was first set up. This capability, sometimes referred to as
partition pruning, was implemented in
- MySQL 5.1.6.
+ MySQL 5.1.6. For additional informaiton, see
+ .
@@ -406,8 +407,8 @@
- Be sure to check this page and chapter frequently for updates as
- Partitioning development for MySQL 5.1 continues.
+ Be sure to check this section and chapter frequently for updates
+ as Partitioning development continues.
@@ -3352,7 +3353,261 @@
+
+
+ &title-partitioning-pruning;
+
+
+ This section discusses partition pruning,
+ an opimisation which was implemented for partitioned tables in
+ MySQL 5.1.6.
+
+
+
+ The core concept behind partitioning is relatively simple, and can
+ be described as Do not scan partitions where there can be
+ no matching values
. For example, suppose you have a
+ partitioned table t1 defined by this statement:
+
+
+
+CREATE TABLE t1 (
+ fname VARCHAR(50) NOT NULL,
+ lname VARCHAR(50) NOT NULL,
+ region_code TINYINT UNSIGNED NOT NULL,
+ dob DATE NOT NULL
+)
+PARTITION BY RANGE( region_code ) (
+ PARTITION p0 VALUES LESS THAN (64),
+ PARTITION p1 VALUES LESS THAN (128),
+ PARTITION p2 VALUES LESS THAN (192)
+ PARTITION p3 VALUES LESS THAN MAXVALUE
+);
+
+
+
+ Consider the case where you wish to obtain results from a query
+ such as this one:
+
+
+SELECT fname, lname, postcode, dob
+ FROM t1
+ WHERE region_code > 125 AND region_code < 130;
+
+
+
+ It is easy to see that none of the rows which ought to be returned
+ will be in either of the partitions p0 or
+ p3; that is, we need to search only in
+ partitions p1 and p2 to find
+ matching rows. By doing so, it is possible to expend much time
+ time and effort in finding matching rows than it is to scan all
+ partitions in the table. This cutting away
of
+ unneeded partitions is known as pruning.
+ When the optimiser can make use of partition pruning in performing
+ a query, execution of the query can be an order of magnitude
+ faster than the same query against a non-partitioned table
+ containing the same column definitions and data.
+
+
+
+ The query optimiser can perform pruning whenever a
+ WHERE condition can be reduced to either one of
+ the following:
+
+
+
+
+
+
+ partition_column =
+ constant
+
+
+
+
+
+ partition_column IN
+ (constant1,
+ constant2, ...,
+ constantN)
+
+
+
+
+
+
+ In the first case, the optimiser simply evaluates evaluates the
+ partitioning expression for the value given, determines which
+ partition contains that value, and scans only this partition. In
+ the second case, the optimiser evaluates the partitioning
+ expression for each value in the list, creates a list of
+ matching partitions, and then scans only the partitions in this
+ partition list.
+
+
+
+ Pruning can also be applied to short ranges, which the optimiser
+ can convert into equivalent lists of values. For instance, in the
+ previous example, the WHERE clause can be
+ converted to WHERE region_code IN (125, 126, 127, 128,
+ 129, 130). Then the optimiser can that the first three
+ values in the list are found in partition p1,
+ the remaining three values in partition p2, and
+ that the other partitions contain no relevant values and so do not
+ need to be searched for matching rows.
+
+
+
+ This type of optimisation can be applied whenever the partitioning
+ expression consists of an equality or a range which can be reduced
+ to a set of equalities. It can also be employed when the
+ partitioning expression represents an incresing or decreasing
+ relationship or uses a function such as YEAR()
+ or TO_DAYS() that produces an integer value
+ when applied to a DATE or
+ DATETIME column value. For example, suppose
+ that table t2, defined as shown here, is
+ partitioned on a DATE column:
+
+
+
+CREATE TABLE t2 (
+ fname VARCHAR(50) NOT NULL,
+ lname VARCHAR(50) NOT NULL,
+ region_code TINYINT UNSIGNED NOT NULL,
+ dob DATE NOT NULL
+)
+PARTITION BY RANGE( YEAR(dob) ) (
+ PARTITION d0 VALUES LESS THAN (1970),
+ PARTITION d1 VALUES LESS THAN (1975),
+ PARTITION d2 VALUES LESS THAN (1980),
+ PARTITION d3 VALUES LESS THAN (1985),
+ PARTITION d4 VALUES LESS THAN (1990),
+ PARTITION d5 VALUES LESS THAN (2000),
+ PARTITION d6 VALUES LESS THAN (2005),
+ PARTITION d7 VALUES LESS THAN MAXVALUE
+);
+
+
+
+ The following queries on t2 can make of use
+ partition pruning:
+
+
+
+SELECT * FROM t2 WHERE dob = '1982-06-23';
+
+SELECT * FROM t2 WHERE dob BETWEEN '1991-02-15' AND '1997-04-25';
+
+SELECT * FROM t2 WHERE YEAR(dob) IN (1979, 1980, 1983, 1985, 1986, 1988);
+
+SELECT * FROM t2 WHERE dob >= '1984-06-21' AND dob <= '1999-06-21'
+
+
+
+ In the case of the last query, the optimiser can also act as
+ follows:
+
+
+
+
+
+
+ Find the partition containing the low end of the
+ range.
+
+
+
+ YEAR('1984-06-21') yields the
+ value 1974, which is found in partition
+ d3.
+
+
+
+
+
+ Find the partition containing the high end of the
+ range.
+
+
+
+ YEAR('1999-06-21') evaluates to
+ 1999, which is found in partition
+ d5.
+
+
+
+
+
+ Scan only these two partitions and any partitions
+ that may lie between them.
+
+
+
+ In this case, this means that only partitions
+ d3, d4, and
+ d5 are scanned. The remaining partitions
+ may be safely ignored (and are ignored).
+
+
+
+
+
+
+ So far, we have looked only at examples using
+ RANGE partitioning, but pruning can be applied
+ with other partitioning types as well.
+
+
+
+ Consider a table that is partitioned by LIST, where
+ the partitioning expression is increasing or decreasing, such as
+ the table t3 shown here. (In this example, we
+ assume for the sake of brevity that the
+ region_code column is limited to values between
+ 1 and 10 inclusive.)
+
+
+
+CREATE TABLE t3 (
+ fname VARCHAR(50) NOT NULL,
+ lname VARCHAR(50) NOT NULL,
+ region_code TINYINT UNSIGNED NOT NULL,
+ dob DATE NOT NULL
+)
+PARTITION BY LIST(region_code) (
+ PARTITION r0 VALUES IN (1, 3),
+ PARTITION r1 VALUES IN (2, 5, 8),
+ PARTITION r2 VALUES IN (4, 9),
+ PARTITION r3 VALUES IN (6, 7, 10)
+);
+
+
+
+ For a query such as SELECT * FROM t3 WHERE region_code
+ BETWEEN 1 AND 3, the optimiser determines in which
+ partitions the values 1, 2, and 3 are found (r0
+ and r1) and skips the remaining ones
+ (r2 and r3).
+
+
+
+ For tables that are partitioned by HASH or
+ KEY, partition pruning is also possible.
+ However, it can be used only on integer columns of tables using
+ these partitioning types. For example, if the table has a
+ DATE column, a query with a
+ WHERE condition such as WHERE date_col
+ >=- '2001-04-14' AND date_col <= '2005-10-15'
+ does not use partition pruning, but if the table stores year
+ values in an INT column, then a query having
+ WHERE year_col >= 2001 AND year_col <=
+ 2005 can be pruned.
+
+
+
&title-partitioning-limitations;
@@ -3397,12 +3652,77 @@
A partitioning key must be either an integer column or an
- expression that resolves to an integer. Currently,
- NULL values are also permitted; however,
- this is subject to change.
+ expression that resolves to an integer. The column or
+ expression value may also be NULL. (See
+ .)
+
+
+
+ If a table that is to be partitioned has a primary key, then
+ any columns used in the partitioning expression must be part
+ of the primary key. For example, the following two statements
+ are invalid:
+
+
+
+CREATE TABLE t1 (
+ col1 INT NOT NULL,
+ col2 DATE NOT NULL,
+ col3 INT NOT NULL,
+ col4 INT NOT NULL,
+ PRIMARY KEY(col1, col2)
+)
+PARTITION BY HASH(col3)
+PARTITIONS 4;
+CREATE TABLE t1 (
+ col1 INT NOT NULL,
+ col2 DATE NOT NULL,
+ col3 INT NOT NULL,
+ col4 INT NOT NULL,
+ PRIMARY KEY(col1, col3)
+)
+PARTITION BY HASH( YEAR(col2) )
+PARTITIONS 4;
+
+
+
+ In both cases, the primary key does not include all columns
+ referenced in the partitioning expression. However, both of
+ the next two statements are valid:
+
+
+
+CREATE TABLE t1 (
+ col1 INT NOT NULL,
+ col2 DATE NOT NULL,
+ col3 INT NOT NULL,
+ col4 INT NOT NULL,
+ PRIMARY KEY(col1, col2)
+)
+PARTITION BY HASH(col1 + YEAR(col2))
+PARTITIONS 4;
+
+CREATE TABLE t1 (
+ col1 INT NOT NULL,
+ col2 DATE NOT NULL,
+ col3 INT NOT NULL,
+ col4 INT NOT NULL,
+ PRIMARY KEY(col1, col2, col4)
+)
+PARTITION BY HASH(col1 + YEAR(col2))
+PARTITIONS 4;
+
+
+
+ If the table does not have a primary key, then this
+ restriction does not apply, and you may use any column or
+ columns in the partitioning expression.
+
+
+
Subpartitions are limited to HASH or
Modified: trunk/refman-5.1/sql-syntax.xml
===================================================================
--- trunk/refman-5.1/sql-syntax.xml 2006-02-24 15:18:24 UTC (rev 1439)
+++ trunk/refman-5.1/sql-syntax.xml 2006-02-24 16:45:03 UTC (rev 1440)
@@ -966,7 +966,9 @@
Note: For partitions that
have not been explicitly named, MySQL automatically provides
the default names p0,
- p1, p2, and so on.
+ p1, p2, and so on. As
+ of MySQL 5.1.7, the same is true with regard to
+ subpartitions.
@@ -1476,7 +1478,7 @@
COMPRESSED CONNECTION CSV FEDERATED FIXED DYNAMIC DEFAULT
DELAY_KEY_WRITE DELETE DIRECTORY INDEX FIRST FOREIGN ROW_FORMAT
FULL FULLTEXT HEAP INNOBASE INNODB ISAM INSERT_METHOD MAX_ROWS
- MIN_ROWS PACK_KEYS PARTIAL MERGE MRG_MYISAM MYISAM NDB
+ MIN_ROWS PACK_KEYS PARTIAL PARTITION MERGE MRG_MYISAM MYISAM NDB
NDBCLUSTER NO REDUNDANT REFERENCES SERIAL WITH PARSER
Modified: trunk/refman-common/news-5.1.xml
===================================================================
--- trunk/refman-common/news-5.1.xml 2006-02-24 15:18:24 UTC (rev 1439)
+++ trunk/refman-common/news-5.1.xml 2006-02-24 16:45:03 UTC (rev 1440)
@@ -143,7 +143,7 @@
Attempting to add a new partition to a table partitioned by a
- unque key would cause an Out of memory
+ unique key would cause an Out of memory
error. (Bug #17169)
Modified: trunk/refman-common/titles.en.ent
===================================================================
--- trunk/refman-common/titles.en.ent 2006-02-24 15:18:24 UTC (rev 1439)
+++ trunk/refman-common/titles.en.ent 2006-02-24 16:45:03 UTC (rev 1440)
@@ -1336,6 +1336,7 @@
HASH and KEY Partitions">
RANGE and LIST Partitions">
+
RANGE Partitioning">