List:Commits« Previous MessageNext Message »
From:jon Date:January 19 2006 8:58am
Subject:svn commit - mysqldoc@docsrva: r925 - trunk/refman-5.1
View as plain text  
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&gt; <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&gt; <userinput>EXPLAIN PARTITIONS SELECT * FROM trb1 WHERE id &lt; 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&gt; <userinput>ALTER TABLE trb1 ADD PRIMARY KEY (id);</userinput>
+Query OK, 10 rows affected (0.03 sec)
+Records: 10  Duplicates: 0  Warnings: 0
+
+mysql&gt; <userinput>EXPLAIN PARTITIONS SELECT * FROM trb1 WHERE id &lt; 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.1jon19 Jan