Author: jstephens
Date: 2006-01-19 09:58:54 +0100 (Thu, 19 Jan 2006)
New Revision: 925
Log:
Documented EXPLAIN PARTITIONS SELECT, fixed conflict in partitioning
chapter.
Modified:
trunk/refman-5.1/optimization.xml
trunk/refman-5.1/partitioning.xml
Modified: trunk/refman-5.1/optimization.xml
===================================================================
--- trunk/refman-5.1/optimization.xml 2006-01-19 04:39:25 UTC (rev 924)
+++ trunk/refman-5.1/optimization.xml 2006-01-19 08:58:54 UTC (rev 925)
@@ -689,7 +689,7 @@
</para>
<programlisting>
-EXPLAIN [EXTENDED] SELECT <replaceable>select_options</replaceable>
+EXPLAIN [EXTENDED | PARTITIONS] SELECT <replaceable>select_options</replaceable>
</programlisting>
<remark role="help-syntax-end"/>
@@ -726,6 +726,15 @@
tables are joined and in which order.
</para>
</listitem>
+
+ <listitem>
+ <para>
+ <literal>EXPLAIN PARTITIONS</literal> is available beginning
+ with MySQL 5.1.5. It is useful only when examining queries
+ involving partitioned tables. For details, see
+ <xref linkend="partitioning-info"/>.
+ </para>
+ </listitem>
</itemizedlist>
@@ -787,6 +796,13 @@
of rewriting and optimization rules, and possibly other notes
about the optimization process.
</para>
+
+ <para>
+ <emphasis role="bold">Note</emphasis>: You cannot use the
+ <literal>EXTENDED</literal> and <literal>PARTITIONS</literal>
+ keywords together in the same <literal>EXPLAIN</literal>
+ statement.
+ </para>
<para>
Each output row from <literal>EXPLAIN</literal> provides
Modified: trunk/refman-5.1/partitioning.xml
===================================================================
--- trunk/refman-5.1/partitioning.xml 2006-01-19 04:39:25 UTC (rev 924)
+++ trunk/refman-5.1/partitioning.xml 2006-01-19 08:58:54 UTC (rev 925)
@@ -31,11 +31,9 @@
which is described in <xref linkend="partitioning-subpartitions"/>.
Methods of adding, removing, and altering partitions in existing
partitioned tables are covered in
- <xref
- linkend="partitioning-management"/>. Table maintenance
+ <xref linkend="partitioning-management"/>. Table maintenance
commands for use with partitioned tables are discussed in
- <xref
- linkend="partitioning-maintenance"/>.
+ <xref linkend="partitioning-maintenance"/>.
</para>
<para>
@@ -2951,10 +2949,18 @@
<indexterm>
<primary>partitioning information commands</primary>
</indexterm>
-
+
<indexterm>
<primary>information about partitions, obtaining</primary>
</indexterm>
+
+ <indexterm>
+ <primary>EXPLAIN used with partitioned tables</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>EXPLAIN PARTITIONS</primary>
+ </indexterm>
<para>
This section discusses obtaining information about existing
@@ -2987,6 +2993,11 @@
)
1 row in set (0.00 sec)
</programlisting>
+
+ <remark role="note">
+ [js] This appears to have been fixed in 5.1.6; waiting for
+ confirmation from Mikael.
+ </remark>
<para>
<emphasis role="bold">Note</emphasis>: Currently the
@@ -2994,6 +3005,12 @@
partitioned by <literal>HASH</literal> or
<literal>KEY</literal>. (Bug #14327)
</para>
+
+ <remark role="note">
+ [js] Last sentence of following para commented out until its
+ determined whether we will actually implement SHOW PARTITION
+ STATUS.
+ </remark>
<para>
<literal>SHOW TABLE STATUS</literal> works with partitioned
@@ -3001,12 +3018,25 @@
tables, except that the <literal>Engine</literal> column always
contains the value <literal>'PARTITION'</literal>. (See
<xref linkend="show-table-status"/>, for more information about
- this command.) To obtain status information for individual
+ this command.) <!-- To obtain status information for individual
partitions, we plan to implement a <literal>SHOW PARTITION
- STATUS</literal> command (see below).
+ STATUS</literal> command (see below). -->
</para>
-
+
<para>
+ You can also obtain information about partitions from
+ <literal>INFORMATION_SCHEMA</literal>, which contains a
+ <literal>PARTITIONS</literal> table. See
+ <xref linkend="partitions-table"/>.
+ </para>
+
+ <remark role="note">
+ [js] The following is commented out until it is determined
+ whether these two statements will actually be implemented.
+ </remark>
+
+<!--
+ <para>
Two additional <literal>SHOW</literal> commands are planned for
use with partitioned tables:
</para>
@@ -3056,14 +3086,159 @@
</listitem>
</itemizedlist>
+-->
+
+ <para>
+ Beginning with MySQL 5.1.5, it is possible to determine which
+ partitions of a partitioned table are involved in a given
+ <literal>SELECT</literal> query using <literal>EXPLAIN
+ PARTITIONS</literal>. The <literal>PARTITIONS</literal>
+ keyword adds a <literal>partitions</literal> column to the
+ output of <literal>EXPLAIN</literal> listing the partitions from
+ which records would be matched by the query.
+ </para>
+
+ <para>
+ Suppose you have a table <literal>trb1</literal> defined and
+ populated as follows:
+ </para>
+
+<programlisting>
+CREATE TABLE trb1 (id INT, name VARCHAR(50), purchased DATE)
+ PARTITION BY RANGE(id)
+ (
+ PARTITION p0 VALUES LESS THAN (3),
+ PARTITION p1 VALUES LESS THAN (7),
+ PARTITION p2 VALUES LESS THAN (9),
+ PARTITION p3 VALUES LESS THAN (11)
+ );
+INSERT INTO trb1 VALUES
+ (1, 'desk organiser', '2003-10-15'),
+ (2, 'CD player', '1993-11-05'),
+ (3, 'TV set', '1996-03-10'),
+ (4, 'bookcase', '1982-01-10'),
+ (5, 'exercise bike', '2004-05-09'),
+ (6, 'sofa', '1987-06-05'),
+ (7, 'popcorn maker', '2001-11-22'),
+ (8, 'aquarium', '1992-08-04'),
+ (9, 'study desk', '1984-09-16'),
+ (10, 'lava lamp', '1998-12-25');
+</programlisting>
+
<para>
- You can also obtain information about partitions from
- <literal>INFORMATION_SCHEMA</literal>, which contains a
- <literal>PARTITIONS</literal> table. See
- <xref linkend="partitions-table"/>.
+ You can see which partitions are used in a query such as
+ <literal>SELECT * FROM trb1;</literal>, as shown here:
</para>
+
+<programlisting>
+mysql> <userinput>EXPLAIN PARTITIONS SELECT * FROM trb1\G</userinput>
+*************************** 1. row ***************************
+ id: 1
+ select_type: SIMPLE
+ table: trb1
+ partitions: p0,p1,p2,p3
+ type: ALL
+possible_keys: NULL
+ key: NULL
+ key_len: NULL
+ ref: NULL
+ rows: 10
+ Extra: Using filesort
+</programlisting>
+
+ <para>
+ In this case, all four partitions are searched. However, when a
+ limiting condition making use of the partitioning key is added
+ to the query, you can see that only those partitions containing
+ matching values are scanned, as shown here:
+ </para>
+
+<programlisting>
+mysql> <userinput>EXPLAIN PARTITIONS SELECT * FROM trb1 WHERE id < 5\G</userinput>
+*************************** 1. row ***************************
+ id: 1
+ select_type: SIMPLE
+ table: trb1
+ partitions: p0,p1
+ type: ALL
+possible_keys: NULL
+ key: NULL
+ key_len: NULL
+ ref: NULL
+ rows: 10
+ Extra: Using where
+</programlisting>
+
+ <para>
+ <literal>EXPLAIN PARTITIONS</literal> provides information about
+ keys used and possible keys, just as with the standard
+ <literal>EXPLAIN SELECT</literal> statement:
+ </para>
+<programlisting>
+mysql> <userinput>ALTER TABLE trb1 ADD PRIMARY KEY (id);</userinput>
+Query OK, 10 rows affected (0.03 sec)
+Records: 10 Duplicates: 0 Warnings: 0
+
+mysql> <userinput>EXPLAIN PARTITIONS SELECT * FROM trb1 WHERE id < 5\G</userinput>
+*************************** 1. row ***************************
+ id: 1
+ select_type: SIMPLE
+ table: trb1
+ partitions: p0,p1
+ type: range
+possible_keys: PRIMARY
+ key: PRIMARY
+ key_len: 4
+ ref: NULL
+ rows: 7
+ Extra: Using where
+</programlisting>
+
+ <para>
+ You should take note of the following restrictions and
+ limitations:
+ </para>
+
+ <itemizedlist>
+ <listitem>
+ <para>
+ You cannot use the <literal>PARTITIONS</literal> and
+ <literal>EXTENDED</literal> keywords together in the same
+ <literal>EXPLAIN ... SELECT</literal> statement. Attempting
+ to do so produces a syntax error.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>EXPLAIN PARTITIONS</literal> provides meaningful
+ results only when employed to examine queries against tables
+ that are partitioned by <literal>RANGE</literal> or
+ <literal>LIST</literal>. (For a table partitioned by
+ <literal>KEY</literal> or <literal>HASH</literal>, all
+ partitions in the table are listed in the
+ <literal>partitions</literal> column of the output.)
+ </para>
+
+ <para>
+ If <literal>EXPLAIN PARTITIONS</literal> is used to examine
+ a query against a non-partitioned table, no error is
+ produced, but the value of the <literal>partitions</literal>
+ column is always <literal>NULL</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>EXPLAIN PARTITIONS</literal> currently works
+ correctly only with tables that are partitioned on a column
+ of an integer datatype.
+ </para>
+ </listitem>
+ </itemizedlist>
+
</section>
</section>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r925 - trunk/refman-5.1 | jon | 19 Jan |
