List:Commits« Previous MessageNext Message »
From:paul.dubois Date:April 4 2011 4:23pm
Subject:svn commit - mysqldoc@oter02: r25721 - in trunk: . refman-5.6
View as plain text  
Author: pd221994
Date: 2011-04-04 18:23:20 +0200 (Mon, 04 Apr 2011)
New Revision: 25721

Log:
 r46690@dhcp-adc-twvpn-1-vpnpool-10-154-14-252:  paul | 2011-04-04 11:12:03 -0500
 Add remaining information for
 WL#5639: Enable Index Condition Pushdown optimizer feature


Modified:
   svk:merge
   trunk/refman-5.6/optimization.xml

Property changes on: trunk
___________________________________________________________________

Modified: svk:merge
===================================================================


Changed blocks: 0, Lines Added: 0, Lines Deleted: 0; 1277 bytes


Modified: trunk/refman-5.6/optimization.xml
===================================================================
--- trunk/refman-5.6/optimization.xml	2011-04-04 16:23:11 UTC (rev 25720)
+++ trunk/refman-5.6/optimization.xml	2011-04-04 16:23:20 UTC (rev 25721)
Changed blocks: 3, Lines Added: 154, Lines Deleted: 2; 6317 bytes

@@ -6987,6 +6987,21 @@
 
             <listitem>
               <para>
+                <literal>Using index condition</literal>
+              </para>
+
+              <para>
+                Tables are read by accessing index tuples and testing
+                them first to determine whether to read full table rows.
+                In this way, index information is used to defer
+                (<quote>push down</quote>) reading full table rows
+                unless it is necessary. See
+                <xref linkend="index-condition-pushdown-optimization"/>.
+              </para>
+            </listitem>
+
+            <listitem>
+              <para>
                 <literal>Using index for group-by</literal>
               </para>
 

@@ -7746,8 +7761,9 @@
           For information about Index Merge and Multi-Range Read, see
           <xref linkend="index-merge-optimization"/> and
           <xref linkend="mrr-optimization"/>. For information about
-          engine condition pushdown, see
-          <xref linkend="condition-pushdown-optimization"/>.
+          condition pushdown, see
+          <xref linkend="condition-pushdown-optimization"/>, and
+          <xref linkend="index-condition-pushdown-optimization"/>.
         </para>
 
         <para>

@@ -17734,6 +17750,142 @@
 
     </section>
 
+    <section id="index-condition-pushdown-optimization">
+
+      <title>Index Condition Pushdown Optimization</title>
+
+      <para>
+        Index Condition Pushdown optimization is used for the
+        <literal role="jointype">range</literal>,
+        <literal role="jointype">ref</literal>,
+        <literal role="jointype">eq_ref</literal>, and
+        <literal role="jointype">ref_or_null</literal> access methods
+        when there is a need to access full table rows. The optimization
+        is that the server tries to use index information to defer
+        (<quote>push down</quote>) reading of full table rows unless it
+        is known to be necessary. This is done by performing early tests
+        on index tuples first. This strategy can be used for
+        <literal>MyISAM</literal> tables.
+      </para>
+
+      <para>
+        To see how this optimization works, consider first how an index
+        scan proceeds when Index Condition Pushdown is not used:
+      </para>
+
+      <orderedlist>
+
+        <listitem>
+          <para>
+            Get the next row, first by reading the index tuple, and then
+            by using the index tuple to locate and read the full table
+            row.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            Test the part of the <literal>WHERE</literal> condition that
+            applies to this table. Accept or reject the row based on the
+            test result.
+          </para>
+        </listitem>
+
+      </orderedlist>
+
+      <para>
+        When Index Condition Pushdown is used, the scan proceeds like
+        this instead:
+      </para>
+
+      <orderedlist>
+
+        <listitem>
+          <para>
+            Get the next rows's index tuple (but not the full table
+            row).
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            Test the part of the <literal>WHERE</literal> condition that
+            applies to this table and can be checked using only index
+            columns. If the condition is not satisfied, proceed to the
+            next row.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            Use the index tuple to locate and read the full table row.
+          </para>
+        </listitem>
+
+        <listitem>
+          <para>
+            Test the part of the <literal>WHERE</literal> condition that
+            applies to this table. Accept or reject the row based on the
+            test result.
+          </para>
+        </listitem>
+
+      </orderedlist>
+
+      <para>
+        When Index Condition Pushdown is used, the
+        <literal>Extra</literal> column in
+        <literal role="stmt">EXPLAIN</literal> output shows
+        <literal>Using index condition</literal>. It will not show
+        <literal>Index only</literal> because that does not apply when
+        full table rows must be read.
+      </para>
+
+      <para>
+        Suppose that we have a table containing information about people
+        and their addresses and that the table has an index defined as
+        <literal>INDEX (zipcode, lastname, firstname)</literal>. If we
+        know a person's <literal>zipcode</literal> value but are not
+        sure about the last name, we can search like this:
+      </para>
+
+<programlisting>
+SELECT * FROM people
+  WHERE zipcode='95054'
+  AND lastname LIKE '%etrunia%'
+  AND address LIKE '%Main Street%'
+</programlisting>
+
+      <para>
+        MySQL can use the index to scan through people with
+        <literal>zipcode='95054'</literal>. The second part
+        (<literal>lastname LIKE '%etrunia%'</literal>) cannot be used to
+        limit the number of rows that must be scanned, so without Index
+        Condition Pushdown, this query must retrieve full table rows for
+        all the people who have <literal>zipcode='95054'</literal>.
+      </para>
+
+      <para>
+        With Index Condition Pushdown, MySQL will check the
+        <literal>lastname LIKE '%etrunia%'</literal> part before reading
+        the full table row. This avoids reading full rows corresponding
+        to all index tuples that do not match the
+        <literal>lastname</literal> condition. The full-row test is
+        <quote>pushed down</quote> to take place later than the
+        index-only test.
+      </para>
+
+      <para>
+        Index Condition Pushdown is enabled by default; it can be
+        controlled with the
+        <literal role="sysvar">engine_condition_pushdown</literal>
+        system variable. This variable also controls table Condition
+        Pushdown as used for NDB; see
+        <xref linkend="condition-pushdown-optimization"/>.
+      </para>
+
+    </section>
+
     <section id="is-null-optimization">
 
       <title><literal role="op">IS NULL</literal> Optimization</title>


Thread
svn commit - mysqldoc@oter02: r25721 - in trunk: . refman-5.6paul.dubois4 Apr