Author: jstephens
Date: 2006-02-27 17:19:18 +0100 (Mon, 27 Feb 2006)
New Revision: 1449
Log:
Fix typo; reformat.
Modified:
trunk/refman-5.1/partitioning.xml
Modified: trunk/refman-5.1/partitioning.xml
===================================================================
--- trunk/refman-5.1/partitioning.xml 2006-02-27 16:09:46 UTC (rev 1448)
+++ trunk/refman-5.1/partitioning.xml 2006-02-27 16:19:18 UTC (rev 1449)
@@ -363,8 +363,8 @@
queries that may not have been so when the partitioning scheme
was first set up. This capability, sometimes referred to as
<firstterm>partition pruning</firstterm>, was implemented in
- MySQL 5.1.6. For additional informaiton, see
- <xref linkend="partitioning-pruning"/>.
+ MySQL 5.1.6. For additional informaiton, see
+ <xref linkend="partitioning-pruning"/>.
</para>
</listitem>
@@ -3353,23 +3353,24 @@
</section>
</section>
-
+
<section id="partitioning-pruning">
+
<title>&title-partitioning-pruning;</title>
-
+
<para>
This section discusses <firstterm>partition pruning</firstterm>,
an opimisation which was implemented for partitioned tables in
MySQL 5.1.6.
</para>
-
+
<para>
The core concept behind partitioning is relatively simple, and can
be described as <quote>Do not scan partitions where there can be
- no matching values</quote>. For example, suppose you have a
- partitioned table <literal>t1</literal> defined by this statement:
+ no matching values</quote>. For example, suppose you have a
+ partitioned table <literal>t1</literal> defined by this statement:
</para>
-
+
<programlisting>
CREATE TABLE t1 (
fname VARCHAR(50) NOT NULL,
@@ -3384,7 +3385,7 @@
PARTITION p3 VALUES LESS THAN MAXVALUE
);
</programlisting>
-
+
<para>
Consider the case where you wish to obtain results from a query
such as this one:
@@ -3395,7 +3396,7 @@
FROM t1
WHERE region_code > 125 AND region_code < 130;
</programlisting>
-
+
<para>
It is easy to see that none of the rows which ought to be returned
will be in either of the partitions <literal>p0</literal> or
@@ -3410,56 +3411,56 @@
faster than the same query against a non-partitioned table
containing the same column definitions and data.
</para>
-
+
<para>
The query optimiser can perform pruning whenever a
<literal>WHERE</literal> condition can be reduced to either one of
the following:
</para>
-
+
<itemizedlist>
-
+
<listitem>
<para>
<literal><replaceable>partition_column</replaceable> =
- <replaceable>constant</replaceable></literal>
+ <replaceable>constant</replaceable></literal>
</para>
</listitem>
-
+
<listitem>
<para>
<literal><replaceable>partition_column</replaceable> IN
- (<replaceable>constant1</replaceable>,
- <replaceable>constant2</replaceable>, ...,
- <replaceable>constantN</replaceable>)</literal>
+ (<replaceable>constant1</replaceable>,
+ <replaceable>constant2</replaceable>, ...,
+ <replaceable>constantN</replaceable>)</literal>
</para>
</listitem>
-
+
</itemizedlist>
-
+
<para>
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.
+ expression for each value in the list, creates a list of matching
+ partitions, and then scans only the partitions in this partition
+ list.
</para>
-
+
<para>
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 <literal>WHERE</literal> clause can be
converted to <literal>WHERE region_code IN (125, 126, 127, 128,
- 129, 130)</literal>. Then the optimiser can determine that the
+ 129, 130)</literal>. Then the optimiser can determine that the
first three values in the list are found in partition
<literal>p1</literal>, the remaining three values in partition
<literal>p2</literal>, and that the other partitions contain no
relevant values and so do not need to be searched for matching
rows.
</para>
-
+
<para>
This type of optimisation can be applied whenever the partitioning
expression consists of an equality or a range which can be reduced
@@ -3472,7 +3473,7 @@
that table <literal>t2</literal>, defined as shown here, is
partitioned on a <literal>DATE</literal> column:
</para>
-
+
<programlisting>
CREATE TABLE t2 (
fname VARCHAR(50) NOT NULL,
@@ -3491,7 +3492,7 @@
PARTITION d7 VALUES LESS THAN MAXVALUE
);
</programlisting>
-
+
<para>
The following queries on <literal>t2</literal> can make of use
partition pruning:
@@ -3506,46 +3507,46 @@
SELECT * FROM t2 WHERE dob >= '1984-06-21' AND dob <= '1999-06-21'
</programlisting>
-
+
<para>
In the case of the last query, the optimiser can also act as
follows:
</para>
-
+
<orderedlist>
-
+
<listitem>
<para>
<emphasis>Find the partition containing the low end of the
- range</emphasis>.
+ range</emphasis>.
</para>
-
+
<para>
- <literal>YEAR('1984-06-21')</literal> yields the
- value <literal>1974</literal>, which is found in partition
+ <literal>YEAR('1984-06-21')</literal> yields the value
+ <literal>1984</literal>, which is found in partition
<literal>d3</literal>.
</para>
</listitem>
-
+
<listitem>
<para>
<emphasis>Find the partition containing the high end of the
- range</emphasis>.
+ range</emphasis>.
</para>
-
+
<para>
<literal>YEAR('1999-06-21')</literal> evaluates to
<literal>1999</literal>, which is found in partition
<literal>d5</literal>.
</para>
</listitem>
-
+
<listitem>
<para>
<emphasis>Scan only these two partitions and any partitions
- that may lie between them</emphasis>.
+ that may lie between them</emphasis>.
</para>
-
+
<para>
In this case, this means that only partitions
<literal>d3</literal>, <literal>d4</literal>, and
@@ -3553,24 +3554,24 @@
may be safely ignored (and are ignored).
</para>
</listitem>
-
+
</orderedlist>
-
+
<para>
So far, we have looked only at examples using
<literal>RANGE</literal> partitioning, but pruning can be applied
with other partitioning types as well.
</para>
-
+
<para>
- Consider a table that is partitioned by <literal>LIST</literal>, where
- the partitioning expression is increasing or decreasing, such as
- the table <literal>t3</literal> shown here. (In this example, we
- assume for the sake of brevity that the
+ Consider a table that is partitioned by <literal>LIST</literal>,
+ where the partitioning expression is increasing or decreasing,
+ such as the table <literal>t3</literal> shown here. (In this
+ example, we assume for the sake of brevity that the
<literal>region_code</literal> column is limited to values between
1 and 10 inclusive.)
</para>
-
+
<programlisting>
CREATE TABLE t3 (
fname VARCHAR(50) NOT NULL,
@@ -3585,15 +3586,15 @@
PARTITION r3 VALUES IN (6, 7, 10)
);
</programlisting>
-
+
<para>
For a query such as <literal>SELECT * FROM t3 WHERE region_code
- BETWEEN 1 AND 3</literal>, the optimiser determines in which
+ BETWEEN 1 AND 3</literal>, the optimiser determines in which
partitions the values 1, 2, and 3 are found (<literal>r0</literal>
and <literal>r1</literal>) and skips the remaining ones
(<literal>r2</literal> and <literal>r3</literal>).
</para>
-
+
<para>
For tables that are partitioned by <literal>HASH</literal> or
<literal>KEY</literal>, partition pruning is also possible.
@@ -3601,14 +3602,15 @@
these partitioning types. For example, if the table has a
<literal>DATE</literal> column, a query with a
<literal>WHERE</literal> condition such as <literal>WHERE date_col
- >=- '2001-04-14' AND date_col <= '2005-10-15'</literal>
- does not use partition pruning, but if the table stores year
- values in an <literal>INT</literal> column, then a query having
+ >=- '2001-04-14' AND date_col <= '2005-10-15'</literal> does
+ not use partition pruning, but if the table stores year values in
+ an <literal>INT</literal> column, then a query having
<literal>WHERE year_col >= 2001 AND year_col <=
- 2005</literal> can be pruned.
+ 2005</literal> can be pruned.
</para>
- </section>
-
+
+ </section>
+
<section id="partitioning-limitations">
<title>&title-partitioning-limitations;</title>
@@ -3654,11 +3656,11 @@
<para>
A partitioning key must be either an integer column or an
expression that resolves to an integer. The column or
- expression value may also be <literal>NULL</literal>. (See
+ expression value may also be <literal>NULL</literal>. (See
<xref linkend="partitioning-handling-nulls"/>.)
</para>
</listitem>
-
+
<listitem>
<para>
If a table that is to be partitioned has a primary key, then
@@ -3666,7 +3668,7 @@
of the primary key. For example, the following two statements
are invalid:
</para>
-
+
<programlisting>
CREATE TABLE t1 (
col1 INT NOT NULL,
@@ -3688,13 +3690,13 @@
PARTITION BY HASH( YEAR(col2) )
PARTITIONS 4;
</programlisting>
-
+
<para>
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:
</para>
-
+
<programlisting>
CREATE TABLE t1 (
col1 INT NOT NULL,
@@ -3716,7 +3718,7 @@
PARTITION BY HASH(col1 + YEAR(col2))
PARTITIONS 4;
</programlisting>
-
+
<para>
If the table does not have a primary key, then this
restriction does not apply, and you may use any column or
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r1449 - trunk/refman-5.1 | jon | 27 Feb |
