Author: jstephens
Date: 2008-02-08 17:44:06 +0100 (Fri, 08 Feb 2008)
New Revision: 9748
Log:
Update of engine condition pushdown description
Added condition pushdown limitations
(Thanks, Martin!)
Modified:
trunk/refman-5.0/optimization.xml
trunk/refman-5.1/optimization.xml
trunk/refman-6.0/optimization.xml
Modified: trunk/refman-5.0/optimization.xml
===================================================================
--- trunk/refman-5.0/optimization.xml 2008-02-08 15:12:36 UTC (rev 9747)
+++ trunk/refman-5.0/optimization.xml 2008-02-08 16:44:06 UTC (rev 9748)
Changed blocks: 3, Lines Added: 135, Lines Deleted: 47; 8676 bytes
@@ -1582,14 +1582,14 @@
tables <emphasis>only</emphasis>. It means that MySQL
Cluster is using <firstterm>condition
pushdown</firstterm> to improve the efficiency of a
- direct comparison (<literal>=</literal>) between a
- non-indexed column and a constant. In such cases, the
- condition is <quote>pushed down</quote> to the cluster's
- data nodes where it is evaluated in all partitions
- simultaneously. This eliminates the need to send
- non-matching rows over the network, and can speed up
- such queries by a factor of 5 to 10 times over cases
- where condition pushdown could be but is not used.
+ direct comparison between a non-indexed column and a
+ constant. In such cases, the condition is <quote>pushed
+ down</quote> to the cluster's data nodes where it is
+ evaluated in all partitions simultaneously. This
+ eliminates the need to send non-matching rows over the
+ network, and can speed up such queries by a factor of 5
+ to 10 times over cases where condition pushdown could be
+ but is not used.
</para>
<para>
@@ -1661,7 +1661,6 @@
an indexed column is compared with a constant using a
<literal>></literal> or <literal><</literal>
operator:
- </para>
<programlisting>
mysql> <userinput>EXPLAIN SELECT a,b FROM t1 WHERE a<2\G</userinput>
@@ -1677,56 +1676,145 @@
rows: 2
Extra: Using where with pushed condition
</programlisting>
+ </para>
<para>
- With regard to condition pushdown, keep in mind that:
+ Other comparisons which are supported for condition
+ pushdown include the following:
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal><replaceable>column</replaceable> LIKE
+ <replaceable>pattern</replaceable></literal> and
+ <literal><replaceable>column</replaceable> NOT
+ LIKE <replaceable>pattern</replaceable></literal>
+ </para>
+
+ <para>
+ <replaceable>pattern</replaceable> must be a
+ string literal containing the pattern to be
+ matched; see
+ <xref linkend="string-comparison-functions"/>, for
+ syntax.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal><replaceable>column</replaceable> IS
+ NULL</literal> and
+ <literal><replaceable>column</replaceable> IS NOT
+ NULL</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal><replaceable>column</replaceable> IN
+ (<replaceable>value-list</replaceable>)</literal>
+ </para>
+
+ <para>
+ Each item in the
+ <replaceable>value-list</replaceable> must be a
+ constant, literal value.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal><replaceable>column</replaceable> BETWEEN
+ <replaceable>constant1</replaceable> AND
+ <replaceable>constant2</replaceable></literal>
+ </para>
+
+ <para>
+ Each of <replaceable>constant1</replaceable> and
+ <replaceable>constant2</replaceable> must be a
+ constant, literal value.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ In each of these cases, it is possible for the condition
+ to be converted into the form of one or more direct
+ comparisons between a column and a constant.
</para>
- <itemizedlist>
+ <para>
+ 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:
- <listitem>
- <para>
- Condition pushdown is relevant to MySQL Cluster
- <emphasis>only</emphasis>, and does not occur when
- executing queries against tables using any other
- storage engine.
- </para>
- </listitem>
-
- <listitem>
- <para>
- 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:
-
- <programlisting>
+<programlisting>
SET engine_condition_pushdown=ON;
</programlisting>
-
- <programlisting>
+
+<programlisting>
SET engine_condition_pushdown=1;
</programlisting>
+ </para>
- <note>
- <para>
- Condition pushdown is not supported for columns of
- any of the <literal>BLOB</literal> or
- <literal>TEXT</literal> types.
- </para>
- </note>
- </para>
- </listitem>
+ <formalpara>
- </itemizedlist>
+ <title>Limitations</title>
- <para>
- Condition pushdown, <literal>Using where with pushed
- condition</literal>, and
- <option>engine_condition_pushdown</option> were all
- introduced in MySQL 5.0 Cluster.
- </para>
+ <para>
+ Condition pushdown is subject to the following
+ limitations:
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Condition pushdown is currently supported by the
+ NDB storage engine <emphasis>only</emphasis>,
+ and does not occur when executing queries
+ against tables using any other storage engine.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Columns may be compared with constant, literal
+ values only; values cannot be replaced with user
+ variables or subselects.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Columns used in comparisons cannot be of any of
+ the <literal>BLOB</literal> or
+ <literal>TEXT</literal> types.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ A string value to be compared with a column must
+ use the same collation as the column.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Joins are not directly supported; conditions
+ involving multiple tables are pushed separately
+ where possible. Use <literal>EXPLAIN
+ EXTENDED</literal> to determine which conditions
+ are actaully pushed down.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </para>
+
+ </formalpara>
</listitem>
</itemizedlist>
Modified: trunk/refman-5.1/optimization.xml
===================================================================
--- trunk/refman-5.1/optimization.xml 2008-02-08 15:12:36 UTC (rev 9747)
+++ trunk/refman-5.1/optimization.xml 2008-02-08 16:44:06 UTC (rev 9748)
Changed blocks: 4, Lines Added: 132, Lines Deleted: 35; 8465 bytes
@@ -1729,14 +1729,14 @@
tables <emphasis>only</emphasis>. It means that MySQL
Cluster is using <firstterm>condition
pushdown</firstterm> to improve the efficiency of a
- direct comparison (<literal>=</literal>) between a
- non-indexed column and a constant. In such cases, the
- condition is <quote>pushed down</quote> to the cluster's
- data nodes where it is evaluated in all partitions
- simultaneously. This eliminates the need to send
- non-matching rows over the network, and can speed up
- such queries by a factor of 5 to 10 times over cases
- where condition pushdown could be but is not used.
+ direct comparison between a non-indexed column and a
+ constant. In such cases, the condition is <quote>pushed
+ down</quote> to the cluster's data nodes where it is
+ evaluated in all partitions simultaneously. This
+ eliminates the need to send non-matching rows over the
+ network, and can speed up such queries by a factor of 5
+ to 10 times over cases where condition pushdown could be
+ but is not used.
</para>
<para>
@@ -1808,7 +1808,6 @@
an indexed column is compared with a constant using a
<literal>></literal> or <literal><</literal>
operator:
- </para>
<programlisting>
mysql> <userinput>EXPLAIN SELECT a,b FROM t1 WHERE a<2\G</userinput>
@@ -1824,31 +1823,83 @@
rows: 2
Extra: Using where with pushed condition
</programlisting>
+ </para>
<para>
- With regard to condition pushdown, keep in mind that:
- </para>
+ Other comparisons which are supported for condition
+ pushdown include the following:
- <itemizedlist>
+ <itemizedlist>
- <listitem>
- <para>
- Condition pushdown is relevant to MySQL Cluster
- <emphasis>only</emphasis>, and does not occur when
- executing queries against tables using any other
- storage engine.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal><replaceable>column</replaceable> LIKE
+ <replaceable>pattern</replaceable></literal> and
+ <literal><replaceable>column</replaceable> NOT
+ LIKE <replaceable>pattern</replaceable></literal>
+ </para>
- <listitem>
- <para>
- Condition pushdown capability is used by default. To
- disable 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:
+ <para>
+ <replaceable>pattern</replaceable> must be a
+ string literal containing the pattern to be
+ matched; see
+ <xref linkend="string-comparison-functions"/>, for
+ syntax.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal><replaceable>column</replaceable> IS
+ NULL</literal> and
+ <literal><replaceable>column</replaceable> IS NOT
+ NULL</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal><replaceable>column</replaceable> IN
+ (<replaceable>value-list</replaceable>)</literal>
+ </para>
+
+ <para>
+ Each item in the
+ <replaceable>value-list</replaceable> must be a
+ constant, literal value.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal><replaceable>column</replaceable> BETWEEN
+ <replaceable>constant1</replaceable> AND
+ <replaceable>constant2</replaceable></literal>
+ </para>
+
+ <para>
+ Each of <replaceable>constant1</replaceable> and
+ <replaceable>constant2</replaceable> must be a
+ constant, literal value.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ In each of these cases, it is possible for the condition
+ to be converted into the form of one or more direct
+ comparisons between a column and a constant.
+ </para>
+
+ <para>
+ Condition pushdown capability is used by default (this
+ is a change from MySQL 5.0, where it had to be enabled).
+ To disable 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:
+
<programlisting>
SET engine_condition_pushdown=OFF;
</programlisting>
@@ -1856,18 +1907,64 @@
<programlisting>
SET engine_condition_pushdown=0;
</programlisting>
+ </para>
- <note>
+ <formalpara>
+
+ <title>Limitations</title>
+
+ <para>
+ Condition pushdown is subject to the following
+ limitations:
+
+ <itemizedlist>
+
+ <listitem>
<para>
- Condition pushdown is not supported for columns
- of any of the <literal>BLOB</literal> or
+ Condition pushdown is currently supported by the
+ NDB storage engine <emphasis>only</emphasis>,
+ and does not occur when executing queries
+ against tables using any other storage engine.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Columns may be compared with constant, literal
+ values only; values cannot be replaced with user
+ variables or subselects.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Columns used in comparisons cannot be of any of
+ the <literal>BLOB</literal> or
<literal>TEXT</literal> types.
</para>
- </note>
- </para>
- </listitem>
+ </listitem>
- </itemizedlist>
+ <listitem>
+ <para>
+ A string value to be compared with a column must
+ use the same collation as the column.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Joins are not directly supported; conditions
+ involving multiple tables are pushed separately
+ where possible. Use <literal>EXPLAIN
+ EXTENDED</literal> to determine which conditions
+ are actaully pushed down.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </para>
+
+ </formalpara>
</listitem>
</itemizedlist>
Modified: trunk/refman-6.0/optimization.xml
===================================================================
--- trunk/refman-6.0/optimization.xml 2008-02-08 15:12:36 UTC (rev 9747)
+++ trunk/refman-6.0/optimization.xml 2008-02-08 16:44:06 UTC (rev 9748)
Changed blocks: 4, Lines Added: 130, Lines Deleted: 35; 8371 bytes
@@ -1727,14 +1727,14 @@
tables <emphasis>only</emphasis>. It means that MySQL
Cluster is using <firstterm>condition
pushdown</firstterm> to improve the efficiency of a
- direct comparison (<literal>=</literal>) between a
- non-indexed column and a constant. In such cases, the
- condition is <quote>pushed down</quote> to the cluster's
- data nodes where it is evaluated in all partitions
- simultaneously. This eliminates the need to send
- non-matching rows over the network, and can speed up
- such queries by a factor of 5 to 10 times over cases
- where condition pushdown could be but is not used.
+ direct comparison between a non-indexed column and a
+ constant. In such cases, the condition is <quote>pushed
+ down</quote> to the cluster's data nodes where it is
+ evaluated in all partitions simultaneously. This
+ eliminates the need to send non-matching rows over the
+ network, and can speed up such queries by a factor of 5
+ to 10 times over cases where condition pushdown could be
+ but is not used.
</para>
<para>
@@ -1806,7 +1806,6 @@
an indexed column is compared with a constant using a
<literal>></literal> or <literal><</literal>
operator:
- </para>
<programlisting>
mysql> <userinput>EXPLAIN SELECT a,b FROM t1 WHERE a<2\G</userinput>
@@ -1822,31 +1821,81 @@
rows: 2
Extra: Using where with pushed condition
</programlisting>
+ </para>
<para>
- With regard to condition pushdown, keep in mind that:
- </para>
+ Other comparisons which are supported for condition
+ pushdown include the following:
- <itemizedlist>
+ <itemizedlist>
- <listitem>
- <para>
- Condition pushdown is relevant to MySQL Cluster
- <emphasis>only</emphasis>, and does not occur when
- executing queries against tables using any other
- storage engine.
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal><replaceable>column</replaceable> LIKE
+ <replaceable>pattern</replaceable></literal> and
+ <literal><replaceable>column</replaceable> NOT
+ LIKE <replaceable>pattern</replaceable></literal>
+ </para>
- <listitem>
- <para>
- Condition pushdown capability is used by default. To
- disable 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:
+ <para>
+ <replaceable>pattern</replaceable> must be a
+ string literal containing the pattern to be
+ matched; see
+ <xref linkend="string-comparison-functions"/>, for
+ syntax.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ <literal><replaceable>column</replaceable> IS
+ NULL</literal> and
+ <literal><replaceable>column</replaceable> IS NOT
+ NULL</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal><replaceable>column</replaceable> IN
+ (<replaceable>value-list</replaceable>)</literal>
+ </para>
+
+ <para>
+ Each item in the
+ <replaceable>value-list</replaceable> must be a
+ constant, literal value.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal><replaceable>column</replaceable> BETWEEN
+ <replaceable>constant1</replaceable> AND
+ <replaceable>constant2</replaceable></literal>
+ </para>
+
+ <para>
+ Each of <replaceable>constant1</replaceable> and
+ <replaceable>constant2</replaceable> must be a
+ constant, literal value.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ In each of these cases, it is possible for the condition
+ to be converted into the form of one or more direct
+ comparisons between a column and a constant.
+ </para>
+
+ <para>
+ Condition pushdown capability is used by default. To
+ disable 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:
+
<programlisting>
SET engine_condition_pushdown=OFF;
</programlisting>
@@ -1854,18 +1903,64 @@
<programlisting>
SET engine_condition_pushdown=0;
</programlisting>
+ </para>
- <note>
+ <formalpara>
+
+ <title>Limitations</title>
+
+ <para>
+ Condition pushdown is subject to the following
+ limitations:
+
+ <itemizedlist>
+
+ <listitem>
<para>
- Condition pushdown is not supported for columns
- of any of the <literal>BLOB</literal> or
+ Condition pushdown is currently supported by the
+ NDB storage engine <emphasis>only</emphasis>,
+ and does not occur when executing queries
+ against tables using any other storage engine.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Columns may be compared with constant, literal
+ values only; values cannot be replaced with user
+ variables or subselects.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Columns used in comparisons cannot be of any of
+ the <literal>BLOB</literal> or
<literal>TEXT</literal> types.
</para>
- </note>
- </para>
- </listitem>
+ </listitem>
- </itemizedlist>
+ <listitem>
+ <para>
+ A string value to be compared with a column must
+ use the same collation as the column.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Joins are not directly supported; conditions
+ involving multiple tables are pushed separately
+ where possible. Use <literal>EXPLAIN
+ EXTENDED</literal> to determine which conditions
+ are actaully pushed down.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </para>
+
+ </formalpara>
</listitem>
</itemizedlist>
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r9748 - in trunk: refman-5.0 refman-5.1 refman-6.0 | jon | 8 Feb |
