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.6 | paul.dubois | 4 Apr |
