Author: paul
Date: 2008-05-28 21:54:26 +0200 (Wed, 28 May 2008)
New Revision: 10855
Log:
r31512@arctic: paul | 2008-05-28 14:54:43 -0500
Initial information about Index Condition Pushdown, Multi Read Range
optimizations.
(WL#2474, WL#2475)
Modified:
trunk/it/refman-5.1/optimization.xml
trunk/pt/refman-5.1/optimization.xml
trunk/refman-5.0/optimization.xml
trunk/refman-5.1/optimization.xml
trunk/refman-6.0/optimization.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:35828
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:31728
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:31499
+ 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:35828
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:31728
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:31512
Modified: trunk/it/refman-5.1/optimization.xml
===================================================================
--- trunk/it/refman-5.1/optimization.xml 2008-05-28 18:40:14 UTC (rev 10854)
+++ trunk/it/refman-5.1/optimization.xml 2008-05-28 19:54:26 UTC (rev 10855)
Changed blocks: 2, Lines Added: 6, Lines Deleted: 6; 1689 bytes
@@ -3377,15 +3377,15 @@
<literal>a</literal>. In the case of the second query, a
condition pushdown cannot be employed because the comparison
involving the non-indexed column <literal>b</literal> is an
- indirect one. (However, it would apply if you were to reduce
- <literal>b + 1 = 10</literal> to <literal>b = 9</literal> in the
+ indirect one. (It would apply if you were to reduce <literal>b +
+ 1 = 10</literal> to <literal>b = 9</literal> in the
<literal>WHERE</literal> clause.)
</para>
<para>
- However, a condition pushdown may also be employed when an
- indexed column is compared with a constant using a
- <literal>></literal> or <literal><</literal> operator:
+ A condition pushdown may also be employed when an indexed column
+ is compared with a constant using a <literal>></literal> or
+ <literal><</literal> operator:
<programlisting>
mysql> <userinput>EXPLAIN SELECT a,b FROM t1 WHERE a<2\G</userinput>
@@ -3472,7 +3472,7 @@
it, you can start <command>mysqld</command> with either
<option>--engine-condition-pushdown=OFF</option> or
<option>--engine-condition-pushdown=0</option>, or you can
- execute either of the following statements:
+ execute either of the following statements at runtime:
<programlisting>
SET engine_condition_pushdown=OFF;
Modified: trunk/pt/refman-5.1/optimization.xml
===================================================================
--- trunk/pt/refman-5.1/optimization.xml 2008-05-28 18:40:14 UTC (rev 10854)
+++ trunk/pt/refman-5.1/optimization.xml 2008-05-28 19:54:26 UTC (rev 10855)
Changed blocks: 2, Lines Added: 6, Lines Deleted: 6; 1689 bytes
@@ -3377,15 +3377,15 @@
<literal>a</literal>. In the case of the second query, a
condition pushdown cannot be employed because the comparison
involving the non-indexed column <literal>b</literal> is an
- indirect one. (However, it would apply if you were to reduce
- <literal>b + 1 = 10</literal> to <literal>b = 9</literal> in the
+ indirect one. (It would apply if you were to reduce <literal>b +
+ 1 = 10</literal> to <literal>b = 9</literal> in the
<literal>WHERE</literal> clause.)
</para>
<para>
- However, a condition pushdown may also be employed when an
- indexed column is compared with a constant using a
- <literal>></literal> or <literal><</literal> operator:
+ A condition pushdown may also be employed when an indexed column
+ is compared with a constant using a <literal>></literal> or
+ <literal><</literal> operator:
<programlisting>
mysql> <userinput>EXPLAIN SELECT a,b FROM t1 WHERE a<2\G</userinput>
@@ -3472,7 +3472,7 @@
it, you can start <command>mysqld</command> with either
<option>--engine-condition-pushdown=OFF</option> or
<option>--engine-condition-pushdown=0</option>, or you can
- execute either of the following statements:
+ execute either of the following statements at runtime:
<programlisting>
SET engine_condition_pushdown=OFF;
Modified: trunk/refman-5.0/optimization.xml
===================================================================
--- trunk/refman-5.0/optimization.xml 2008-05-28 18:40:14 UTC (rev 10854)
+++ trunk/refman-5.0/optimization.xml 2008-05-28 19:54:26 UTC (rev 10855)
Changed blocks: 2, Lines Added: 6, Lines Deleted: 6; 1693 bytes
@@ -3230,15 +3230,15 @@
<literal>a</literal>. In the case of the second query, a
condition pushdown cannot be employed because the comparison
involving the non-indexed column <literal>b</literal> is an
- indirect one. (However, it would apply if you were to reduce
- <literal>b + 1 = 10</literal> to <literal>b = 9</literal> in the
+ indirect one. (It would apply if you were to reduce <literal>b +
+ 1 = 10</literal> to <literal>b = 9</literal> in the
<literal>WHERE</literal> clause.)
</para>
<para>
- However, a condition pushdown may also be employed when an
- indexed column is compared with a constant using a
- <literal>></literal> or <literal><</literal> operator:
+ A condition pushdown may also be employed when an indexed column
+ is compared with a constant using a <literal>></literal> or
+ <literal><</literal> operator:
<programlisting>
mysql> <userinput>EXPLAIN SELECT a,b FROM t1 WHERE a<2\G</userinput>
@@ -3323,7 +3323,7 @@
Condition pushdown capability is not used by default. To enable
it, you can start <command>mysqld</command> with the
<option>--engine-condition-pushdown</option> option, or you can
- execute either of the following statements:
+ execute either of the following statements at runtime:
<programlisting>
SET engine_condition_pushdown=ON;
Modified: trunk/refman-5.1/optimization.xml
===================================================================
--- trunk/refman-5.1/optimization.xml 2008-05-28 18:40:14 UTC (rev 10854)
+++ trunk/refman-5.1/optimization.xml 2008-05-28 19:54:26 UTC (rev 10855)
Changed blocks: 2, Lines Added: 6, Lines Deleted: 6; 1680 bytes
@@ -3377,15 +3377,15 @@
<literal>a</literal>. In the case of the second query, a
condition pushdown cannot be employed because the comparison
involving the non-indexed column <literal>b</literal> is an
- indirect one. (However, it would apply if you were to reduce
- <literal>b + 1 = 10</literal> to <literal>b = 9</literal> in the
+ indirect one. (It would apply if you were to reduce <literal>b +
+ 1 = 10</literal> to <literal>b = 9</literal> in the
<literal>WHERE</literal> clause.)
</para>
<para>
- However, a condition pushdown may also be employed when an
- indexed column is compared with a constant using a
- <literal>></literal> or <literal><</literal> operator:
+ A condition pushdown may also be employed when an indexed column
+ is compared with a constant using a <literal>></literal> or
+ <literal><</literal> operator:
<programlisting>
mysql> <userinput>EXPLAIN SELECT a,b FROM t1 WHERE a<2\G</userinput>
@@ -3472,7 +3472,7 @@
it, you can start <command>mysqld</command> with either
<option>--engine-condition-pushdown=OFF</option> or
<option>--engine-condition-pushdown=0</option>, or you can
- execute either of the following statements:
+ execute either of the following statements at runtime:
<programlisting>
SET engine_condition_pushdown=OFF;
Modified: trunk/refman-6.0/optimization.xml
===================================================================
--- trunk/refman-6.0/optimization.xml 2008-05-28 18:40:14 UTC (rev 10854)
+++ trunk/refman-6.0/optimization.xml 2008-05-28 19:54:26 UTC (rev 10855)
Changed blocks: 6, Lines Added: 261, Lines Deleted: 6; 11250 bytes
@@ -1639,6 +1639,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 reading
+ full table rows unless it is necessary. For more
+ information, see
+ <xref linkend="index-condition-pushdown-optimization"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
<literal>Using index for group-by</literal>
</para>
@@ -1670,6 +1685,18 @@
<listitem>
<para>
+ <literal>Using MRR</literal>
+ </para>
+
+ <para>
+ Tables are read using the Multi Read Range optimization
+ strategy. For more information, see
+ <xref linkend="mrr-optimization"/>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
<literal>Using sort_union(...)</literal>, <literal>Using
union(...)</literal>, <literal>Using
intersect(...)</literal>
@@ -3375,15 +3402,15 @@
<literal>a</literal>. In the case of the second query, a
condition pushdown cannot be employed because the comparison
involving the non-indexed column <literal>b</literal> is an
- indirect one. (However, it would apply if you were to reduce
- <literal>b + 1 = 10</literal> to <literal>b = 9</literal> in the
+ indirect one. (It would apply if you were to reduce <literal>b +
+ 1 = 10</literal> to <literal>b = 9</literal> in the
<literal>WHERE</literal> clause.)
</para>
<para>
- However, a condition pushdown may also be employed when an
- indexed column is compared with a constant using a
- <literal>></literal> or <literal><</literal> operator:
+ A condition pushdown may also be employed when an indexed column
+ is compared with a constant using a <literal>></literal> or
+ <literal><</literal> operator:
<programlisting>
mysql> <userinput>EXPLAIN SELECT a,b FROM t1 WHERE a<2\G</userinput>
@@ -3469,7 +3496,7 @@
you can start <command>mysqld</command> with either
<option>--engine-condition-pushdown=OFF</option> or
<option>--engine-condition-pushdown=0</option>, or you can
- execute either of the following statements:
+ execute either of the following statements at runtime:
<programlisting>
SET engine_condition_pushdown=OFF;
@@ -3480,6 +3507,12 @@
</programlisting>
</para>
+ <para>
+ The <literal>engine_condition_pushdown</literal> system variable
+ also controls use of the Index Condition Pushdown strategy; see
+ <xref linkend="index-condition-pushdown-optimization"/>.
+ </para>
+
<formalpara>
<title>Limitations</title>
@@ -3537,6 +3570,228 @@
</section>
+ <section id="index-condition-pushdown-optimization">
+
+ <title>Index Condition Pushdown Optimization</title>
+
+ <para>
+ Index Condition Pushdown optimization is used for the
+ <literal>range</literal>, <literal>ref</literal>,
+ <literal>eq_ref</literal>, and <literal>ref_or_null</literal>
+ access methods when there is a need to access full table rows.
+ The idea is to defer (<quote>push down</quote>) reading of full
+ table rows by performing early tests on index tuples first. This
+ strategy can be used for <literal>MyISAM</literal> tables.
+ </para>
+
+ <para>
+ To see how this works, consider first how an index scan proceeds
+ when Index Condition Pushdown optimization 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 the Index Condition Pushdown optimization 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>
+ In essence, the Index Condition Pushdown optimization is that
+ the server tries to use index information to defer reading full
+ table rows unless it is necessary.
+ </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 records 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>
+ When Index Condition Pushdown is used, the
+ <literal>Extra</literal> column in <literal>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>
+ Index Condition Pushdown is enabled by default; it can be
+ controlled with the <literal>engine_condition_pushdown</literal>
+ system variable. That variable also controls table condition
+ pushdown that is used for NDB; see
+ <xref linkend="condition-pushdown-optimization"/>.
+ </para>
+
+ </section>
+
+ <section id="mrr-optimization">
+
+ <title>Multi Read Range Optimization</title>
+
+ <para>
+ To be added
+ </para>
+
+ <remark role="todo">
+ 1) Use A, #2474, p3 as intro. 2) Combine with #2475, p3, Intro,
+ para 2ff. 3) Example from #2474, p5, Limitations. Why more
+ efficient: scans fewer index rows. (So that's in addition to
+ batching access, I guess.
+ </remark>
+
+ <para>
+ When the Multi Read Range (MRR) optimization is used, the
+ <literal>Extra</literal> column in <literal>EXPLAIN</literal>
+ output shows <literal>Using MRR</literal>. This can occur for
+ <literal>MyISAM</literal> tables for index range scans and
+ equi-join operations. It can occur for <literal>NDB</literal>
+ tables for multiple-range index scans and when performing an
+ equi-join by an attribute.
+ </para>
+
+ <para>
+ MRR enables data rows to be accessed sequentially rather than in
+ random order, based on index tuples. The server obtains a set of
+ index tuples that satisfy the query conditions, sorts them
+ according to data row ID order, and uses the sorted tuples to
+ retrieve table rows. This makes data access more efficient and
+ less expensive.
+ </para>
+
+ <remark role="todo">
+ Add something about batch access here?
+ </remark>
+
+ <para>
+ For MRR, a storage engine uses the value of the
+ <literal>read_rnd_buffer_size</literal> system variable as a
+ guideline for how much memory it can allocate for its buffer.
+ The engine uses up to <literal>read_rnd_buffer_size</literal>
+ bytes and determines the number of ranges to process in a single
+ pass.
+ </para>
+
+ <para>
+ Example query for which MRR can be used:
+ </para>
+
+<programlisting>
+SELECT * FROM t
+ WHERE <replaceable>key_part1</replaceable> >= 1000 AND <replaceable>key_part1</replaceable> < 2000
+ AND <replaceable>key_part2</replaceable> = 10000;
+</programlisting>
+
+ <para>
+ Using MRR, the query is executed using multiple ranges, each for
+ a single value of <replaceable>key_part1</replaceable>. This
+ enables fewer rows to be scanned.
+ </para>
+
+ <remark role="todo">
+ Use more info directly from WL
+ </remark>
+
+ <para>
+ <literal>MyISAM</literal> does not use MRR if full table records
+ need not be accessed to produce the query result. This is the
+ case if results can be produced entirely on the basis on
+ information in the index tuples; MRR provides no benefit.
+ </para>
+
+ <para>
+ The <literal>optimizer_use_mrr</literal> system variable
+ provides an interface to the use of MRR optimization. It exists
+ as a global and session variable. It has supported values of
+ <literal>force</literal> and <literal>disable</literal>. The
+ default value is <literal>force</literal>. That is, MRR is used
+ whenever it is applicable.
+ </para>
+
+ </section>
+
<section id="is-null-optimization">
<title><function role="sqlop">IS NULL</function> Optimization</title>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r10855 - in trunk: . it/refman-5.1 pt/refman-5.1 refman-5.0 refman-5.1 refman-6.0 | paul | 28 May |
