| List: | Internals | « Previous MessageNext Message » | |
| From: | jstephens | Date: | November 26 2005 5:10pm |
| Subject: | svn commit - mysqldoc@docsrva: r418 - in trunk: refman-5.0 refman-5.1 | ||
| View as plain text | |||
Author: jstephens
Date: 2005-11-26 17:09:57 +0100 (Sat, 26 Nov 2005)
New Revision: 418
Log:
refman-5.0/optimization.xml,
refman-5.1/optimization.xml:
- Added discussion of condition pushdown to EXPLAIN section
(Info from SergeyP - Fixes Bug #14472)
refman-5.0/introduction.xml,
refman-5.0/ndbcluster.xml:
- Added links to above
Modified:
trunk/refman-5.0/introduction.xml
trunk/refman-5.0/ndbcluster.xml
trunk/refman-5.0/optimization.xml
trunk/refman-5.1/optimization.xml
Modified: trunk/refman-5.0/introduction.xml
===================================================================
--- trunk/refman-5.0/introduction.xml 2005-11-25 19:35:35 UTC (rev 417)
+++ trunk/refman-5.0/introduction.xml 2005-11-26 16:09:57 UTC (rev 418)
@@ -695,6 +695,10 @@
over the network. This can make such queries 10 to
100 times faster than in MySQL 4.1 Cluster.
</para>
+
+ <para>
+ See <xref linkend="explain"/> for more information.
+ </para>
</listitem>
</itemizedlist>
Modified: trunk/refman-5.0/ndbcluster.xml
===================================================================
--- trunk/refman-5.0/ndbcluster.xml 2005-11-25 19:35:35 UTC (rev 417)
+++ trunk/refman-5.0/ndbcluster.xml 2005-11-26 16:09:57 UTC (rev 418)
@@ -9255,15 +9255,15 @@
<title
id="title-mysql-5-0-cluster-changes">&title-mysql-5-0-cluster-changes;</title>
<para>
- MySQL 5.0.3-beta and later releases contain a number of new
- features that are likely to be of interest:
+ MySQL Cluster in versions 5.0.3-beta and later contains a number
+ of new features that are likely to be of interest:
</para>
<itemizedlist>
<listitem>
<para>
- <emphasis role="bold">Push-Down Conditions</emphasis>: A
+ <emphasis role="bold">Condition Pushdown</emphasis>: A
query such as
</para>
@@ -9276,31 +9276,29 @@
evaluated in the cluster's data nodes. Thus it is not
necessary to send the records across the network for
evaluation. (That is, function transport is used, rather
- than data transport.) For this type of query you should see
- a speed up factor of 5-10. Please note that this feature is
- currently disabled by default (pending more thorough
- testing), but it should work in most cases. This feature can
- be enabled through the use of the command <literal>SET
- engine-condition-pushdown=On;</literal> command.
+ than data transport.) Please note that this feature is
+ disabled by default, but it should work in most cases. This
+ feature can be enabled through the use of the command
+ <literal>SET engine-condition-pushdown=On;</literal>.
Alternatively, you can run <command>mysqld</command> with
- the this feature enabled by starting the MySQL server with
- the new <option>--engine-condition-pushdown</option> option
- flag.
+ this feature enabled by starting the MySQL server with
+ the <option>--engine-condition-pushdown</option> option.
</para>
<para>
- You can use <literal>EXPLAIN</literal> to determine when
- push-down conditions are being used.
- </para>
-
- <para>
- A major benefit of this change is that queries are now
+ A major benefit of this change is that queries can be
executed in parallel. This means that queries against
non-indexed columns can run as much as 5 to 10 times,
<emphasis>times the number of data nodes</emphasis>, faster
than previously, since multiple CPUs can work on the query
in parallel.
</para>
+
+ <para>
+ You can use <literal>EXPLAIN</literal> to determine when
+ condition pushdown is being used. See
+ <xref linkend="explain"/>.
+ </para>
</listitem>
<listitem>
Modified: trunk/refman-5.0/optimization.xml
===================================================================
--- trunk/refman-5.0/optimization.xml 2005-11-25 19:35:35 UTC (rev 417)
+++ trunk/refman-5.0/optimization.xml 2005-11-26 16:09:57 UTC (rev 418)
@@ -1544,7 +1544,146 @@
<xref linkend="group-by-optimization"/>.
</para>
</listitem>
+
+ <listitem>
+ <para>
+ <emphasis role="bold">Note</emphasis>: This item applies
+ to <literal>NDB Cluster</literal> tables
+ <emphasis>only</emphasis>.
+ </para>
+
+ <para>
+ <literal>Using where with pushed condition</literal>
+ </para>
+
+ <para>
+ This means that MySQL Cluster is using
+ <firstterm>condition pushdown</firstterm> to improve the
+ efficiency of a direct comparison
+ (<literal>=</literal>) between a nonindexed
+ 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 records 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>
+ Suppose that you have a Cluster table defined as follows:
+ </para>
+
+<programlisting>
+CREATE TABLE t1 (
+ a INT,
+ b INT,
+ KEY(a)
+) ENGINE=NDBCLUSTER;
+</programlisting>
+
+ <para>
+ In this case, condition pushdown can be used with a
+ query such as this one:
+ </para>
+
+<programlisting>
+SELECT a,b FROM t1 WHERE b = 10;
+</programlisting>
+
+ <para>
+ This can be seen in the output of <literal>EXPLAIN
+ SELECT</literal>, as shown here:
+ </para>
+
+<programlisting>
+mysql> <userinput>EXPLAIN SELECT a,b FROM t1 WHERE b = 10;</userinput>
++----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------+
+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
++----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------+
+| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 10 | Using
where with pushed condition |
++----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------+
+</programlisting>
+
+ <para>
+ Condition pushdown <emphasis>cannot</emphasis> be used
+ with either of these two queries:
+ </para>
+
+<programlisting>
+SELECT a,b FROM t1 WHERE a = 10;
+SELECT a,b FROM t1 WHERE b + 1 = 10;
+</programlisting>
+
+ <para>
+ With regard to the first of these two queries, condition
+ pushdown is not applicable because an index exists on
+ column <literal>a</literal>. In the case of the second
+ query, a condition pushdown cannot be employed because
+ the comparison involving the unindexed column
+ <literal>b</literal> is an indirect one. (However, it
+ would apply, were you 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 with
+ an indexed column when this column is compared with a
+ constant using a <literal>></literal> or
+ <literal><</literal> relation:
+ </para>
+<programlisting>
+mysql> <userinput>EXPLAIN SELECT a,b FROM t1 WHERE
a<2;</userinput>
++----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------+
+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
++----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------+
+| 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 2 | Using
where with pushed condition |
++----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------+
+</programlisting>
+
+ <para>
+ With regard to condition pushdown, you should keep in
+ mind that:
+ </para>
+
+ <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>
+ Condition pushdown capability is not used by
+ default, and must be explicitly enabled. You can do
+ this by executing the statement
+ </para>
+
+<programlisting>
+SET engine-condition-pushdown=On;
+</programlisting>
+
+ <para>
+ or by starting <command>mysqld</command> with
+ <option>--engine-condition-pushdown</option>.
+ </para>
+ </listitem>
+ </itemizedlist>
+
+ <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>
+ </listitem>
+
</itemizedlist>
</listitem>
Modified: trunk/refman-5.1/optimization.xml
===================================================================
--- trunk/refman-5.1/optimization.xml 2005-11-25 19:35:35 UTC (rev 417)
+++ trunk/refman-5.1/optimization.xml 2005-11-26 16:09:57 UTC (rev 418)
@@ -1545,6 +1545,145 @@
</para>
</listitem>
+ <listitem>
+ <para>
+ <emphasis role="bold">Note</emphasis>: This item applies
+ to <literal>NDB Cluster</literal> tables
+ <emphasis>only</emphasis>.
+ </para>
+
+ <para>
+ <literal>Using where with pushed condition</literal>
+ </para>
+
+ <para>
+ This means that MySQL Cluster is using
+ <firstterm>condition pushdown</firstterm> to improve the
+ efficiency of a direct comparison
+ (<literal>=</literal>) between a nonindexed
+ 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 records 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>
+ Suppose that you have a Cluster table defined as follows:
+ </para>
+
+<programlisting>
+CREATE TABLE t1 (
+ a INT,
+ b INT,
+ KEY(a)
+) ENGINE=NDBCLUSTER;
+</programlisting>
+
+ <para>
+ In this case, condition pushdown can be used with a
+ query such as this one:
+ </para>
+
+<programlisting>
+SELECT a,b FROM t1 WHERE b = 10;
+</programlisting>
+
+ <para>
+ This can be seen in the output of <literal>EXPLAIN
+ SELECT</literal>, as shown here:
+ </para>
+
+<programlisting>
+mysql> <userinput>EXPLAIN SELECT a,b FROM t1 WHERE b = 10;</userinput>
++----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------+
+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
++----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------+
+| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 10 | Using
where with pushed condition |
++----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------+
+</programlisting>
+
+ <para>
+ Condition pushdown <emphasis>cannot</emphasis> be used
+ with either of these two queries:
+ </para>
+
+<programlisting>
+SELECT a,b FROM t1 WHERE a = 10;
+SELECT a,b FROM t1 WHERE b + 1 = 10;
+</programlisting>
+
+ <para>
+ With regard to the first of these two queries, condition
+ pushdown is not applicable because an index exists on
+ column <literal>a</literal>. In the case of the second
+ query, a condition pushdown cannot be employed because
+ the comparison involving the unindexed column
+ <literal>b</literal> is an indirect one. (However, it
+ would apply, were you 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 with
+ an indexed column when this column is compared with a
+ constant using a <literal>></literal> or
+ <literal><</literal> relation:
+ </para>
+
+<programlisting>
+mysql> <userinput>EXPLAIN SELECT a,b FROM t1 WHERE
a<2;</userinput>
++----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------+
+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
|
++----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------+
+| 1 | SIMPLE | t1 | range | a | a | 5 | NULL | 2 | Using
where with pushed condition |
++----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------------+
+</programlisting>
+
+ <para>
+ With regard to condition pushdown, you should keep in
+ mind that:
+ </para>
+
+ <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>
+ Condition pushdown capability is not used by
+ default, and must be explicitly enabled. You can do
+ this by executing the statement
+ </para>
+
+<programlisting>
+SET engine-condition-pushdown=On;
+</programlisting>
+
+ <para>
+ or by starting <command>mysqld</command> with
+ <option>--engine-condition-pushdown</option>.
+ </para>
+ </listitem>
+ </itemizedlist>
+
+ <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>
+ </listitem>
+
</itemizedlist>
</listitem>
| Thread | ||
|---|---|---|
| • svn commit - mysqldoc@docsrva: r418 - in trunk: refman-5.0 refman-5.1 | jstephens | 26 Nov |
© 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Page generated in 0.479 sec. using MySQL 5.1.14-beta-log