Author: paul
Date: 2006-02-07 20:51:19 +0100 (Tue, 07 Feb 2006)
New Revision: 1230
Log:
r2917@kite-hub: paul | 2006-02-07 13:16:40 -0600
Add list markup.
Modified:
trunk/
trunk/refman-4.1/restrictions.xml
trunk/refman-5.0/restrictions.xml
trunk/refman-5.1/restrictions.xml
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:7186
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2915
+ b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:7186
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2917
Modified: trunk/refman-4.1/restrictions.xml
===================================================================
--- trunk/refman-4.1/restrictions.xml 2006-02-07 18:51:11 UTC (rev 1229)
+++ trunk/refman-4.1/restrictions.xml 2006-02-07 19:51:19 UTC (rev 1230)
@@ -31,301 +31,334 @@
<secondary>subqueries</secondary>
</indexterm>
- <para>
- Known bug to be fixed later: If you compare a
- <literal>NULL</literal> value to a subquery using
- <literal>ALL</literal>, <literal>ANY</literal>, or
- <literal>SOME</literal>, and the subquery returns an empty result,
- the comparison might evaluate to the non-standard result of
- <literal>NULL</literal> rather than to <literal>TRUE</literal> or
- <literal>FALSE</literal>. This is to be fixed in MySQL 5.1.
- </para>
+ <itemizedlist>
- <para>
- A subquery's outer statement can be any one of:
- <literal>SELECT</literal>, <literal>INSERT</literal>,
- <literal>UPDATE</literal>, <literal>DELETE</literal>,
- <literal>SET</literal>, or <literal>DO</literal>.
- </para>
+ <listitem>
+ <para>
+ Known bug to be fixed later: If you compare a
+ <literal>NULL</literal> value to a subquery using
+ <literal>ALL</literal>, <literal>ANY</literal>, or
+ <literal>SOME</literal>, and the subquery returns an empty
+ result, the comparison might evaluate to the non-standard
+ result of <literal>NULL</literal> rather than to
+ <literal>TRUE</literal> or <literal>FALSE</literal>. This is
+ to be fixed in MySQL 5.1.
+ </para>
+ </listitem>
- <para>
- In general, you cannot modify a table and select from the same
- table in a subquery. For example, this limitation applies to
- statements of the following forms:
- </para>
+ <listitem>
+ <para>
+ A subquery's outer statement can be any one of:
+ <literal>SELECT</literal>, <literal>INSERT</literal>,
+ <literal>UPDATE</literal>, <literal>DELETE</literal>,
+ <literal>SET</literal>, or <literal>DO</literal>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ In general, you cannot modify a table and select from the same
+ table in a subquery. For example, this limitation applies to
+ statements of the following forms:
+ </para>
+
<programlisting>
DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);
</programlisting>
- <para>
- Exception: The preceding prohibition does not apply if you are
- using a subquery for the modified table in the
- <literal>FROM</literal> clause. Example:
- </para>
+ <para>
+ Exception: The preceding prohibition does not apply if you are
+ using a subquery for the modified table in the
+ <literal>FROM</literal> clause. Example:
+ </para>
<programlisting>
UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...) AS _t ...);
</programlisting>
- <para>
- Here the prohibition does not apply because a subquery in the
- <literal>FROM</literal> clause is materialized as a temporary
- table, so the relevant rows in <literal>t</literal> have already
- been selected by the time the update to <literal>t</literal> takes
- place.
- </para>
-
- <para>
- Row comparison operations are only partially supported:
- </para>
-
- <itemizedlist>
-
- <listitem>
<para>
- For <literal><replaceable>expr</replaceable> IN
- (<replaceable>subquery</replaceable>)</literal>,
- <replaceable>expr</replaceable> can be an
- <replaceable>n</replaceable>-tuple (specified via row
- constructor syntax) and the subquery can return rows of
- <replaceable>n</replaceable>-tuples.
+ Here the prohibition does not apply because a subquery in the
+ <literal>FROM</literal> clause is materialized as a temporary
+ table, so the relevant rows in <literal>t</literal> have
+ already been selected by the time the update to
+ <literal>t</literal> takes place.
</para>
</listitem>
<listitem>
<para>
- For <literal><replaceable>expr</replaceable>
- <replaceable>op</replaceable> {ALL|ANY|SOME}
- (<replaceable>subquery</replaceable>)</literal>,
- <replaceable>expr</replaceable> must be a scalar value and the
- subquery must be a column subquery; it cannot return
- multiple-column rows.
+ Row comparison operations are only partially supported:
</para>
- </listitem>
- </itemizedlist>
+ <itemizedlist>
- <para>
- In other words, for a subquery that returns rows of
- <replaceable>n</replaceable>-tuples, this is supported:
- </para>
+ <listitem>
+ <para>
+ For <literal><replaceable>expr</replaceable> IN
+ (<replaceable>subquery</replaceable>)</literal>,
+ <replaceable>expr</replaceable> can be an
+ <replaceable>n</replaceable>-tuple (specified via row
+ constructor syntax) and the subquery can return rows of
+ <replaceable>n</replaceable>-tuples.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ For <literal><replaceable>expr</replaceable>
+ <replaceable>op</replaceable> {ALL|ANY|SOME}
+ (<replaceable>subquery</replaceable>)</literal>,
+ <replaceable>expr</replaceable> must be a scalar value and
+ the subquery must be a column subquery; it cannot return
+ multiple-column rows.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ In other words, for a subquery that returns rows of
+ <replaceable>n</replaceable>-tuples, this is supported:
+ </para>
+
<programlisting>
(<replaceable>val_1</replaceable>, ..., <replaceable>val_n</replaceable>) IN (<replaceable>subquery</replaceable>)
</programlisting>
- <para>
- But this is not supported:
- </para>
+ <para>
+ But this is not supported:
+ </para>
<programlisting>
(<replaceable>val_1</replaceable>, ..., <replaceable>val_n</replaceable>) <replaceable>op</replaceable> {ALL|ANY|SOME} (<replaceable>subquery</replaceable>)
</programlisting>
- <para>
- The reason for supporting row comparisons for
- <literal>IN</literal> but not for the others is that
- <literal>IN</literal> is implemented by rewriting it as a sequence
- of <literal>=</literal> comparisons and <literal>AND</literal>
- operations. This approach cannot be used for
- <literal>ALL</literal>, <literal>ANY</literal>, or
- <literal>SOME</literal>.
- </para>
+ <para>
+ The reason for supporting row comparisons for
+ <literal>IN</literal> but not for the others is that
+ <literal>IN</literal> is implemented by rewriting it as a
+ sequence of <literal>=</literal> comparisons and
+ <literal>AND</literal> operations. This approach cannot be
+ used for <literal>ALL</literal>, <literal>ANY</literal>, or
+ <literal>SOME</literal>.
+ </para>
+ </listitem>
- <para>
- Row constructors are not well optimized. The following two
- expressions are equivalent, but only the second can be optimized:
- </para>
+ <listitem>
+ <para>
+ Row constructors are not well optimized. The following two
+ expressions are equivalent, but only the second can be
+ optimized:
+ </para>
<programlisting>
(col1, col2, ...) = (val1, val2, ...)
col1 = val1 AND col2 = val2 AND ...
</programlisting>
+ </listitem>
- <para>
- Subquery optimization for <literal>IN</literal> is not as
- effective as for the <literal>=</literal> operator.
- </para>
+ <listitem>
+ <para>
+ Subquery optimization for <literal>IN</literal> is not as
+ effective as for the <literal>=</literal> operator.
+ </para>
- <para>
- A typical case for poor <literal>IN</literal> performance is when
- the subquery returns a small number of rows but the outer query
- returns a large number of rows to be compared to the subquery
- result.
- </para>
+ <para>
+ A typical case for poor <literal>IN</literal> performance is
+ when the subquery returns a small number of rows but the outer
+ query returns a large number of rows to be compared to the
+ subquery result.
+ </para>
+ </listitem>
- <para>
- Subqueries in the <literal>FROM</literal> clause cannot be
- correlated subqueries. They are materialized (executed to produce
- a result set) before evaluating the outer query, so they cannot be
- evaluated per row of the outer query.
- </para>
+ <listitem>
+ <para>
+ Subqueries in the <literal>FROM</literal> clause cannot be
+ correlated subqueries. They are materialized (executed to
+ produce a result set) before evaluating the outer query, so
+ they cannot be evaluated per row of the outer query.
+ </para>
+ </listitem>
- <para>
- The optimizer is more mature for joins than for subqueries, so in
- many cases a statement that uses a subquery can be executed more
- efficiently if you rewrite it as a join.
- </para>
+ <listitem>
+ <para>
+ The optimizer is more mature for joins than for subqueries, so
+ in many cases a statement that uses a subquery can be executed
+ more efficiently if you rewrite it as a join.
+ </para>
- <para>
- An exception occurs for the case where an <literal>IN</literal>
- subquery can be rewritten as a <literal>SELECT DISTINCT</literal>
- join. Example:
- </para>
+ <para>
+ An exception occurs for the case where an
+ <literal>IN</literal> subquery can be rewritten as a
+ <literal>SELECT DISTINCT</literal> join. Example:
+ </para>
<programlisting>
SELECT col FROM t1 WHERE id_col IN (SELECT id_col2 FROM t2 WHERE <replaceable>condition</replaceable>);
</programlisting>
- <para>
- That statement can be rewritten as follows:
- </para>
+ <para>
+ That statement can be rewritten as follows:
+ </para>
<programlisting>
SELECT DISTINCT col FROM t1, t2 WHERE t1.id_col = t2.id_col AND <replaceable>condition</replaceable>;
</programlisting>
- <para>
- But in this case, the join requires an extra
- <literal>DISTINCT</literal> operation and is not more efficient
- than the subquery.
- </para>
+ <para>
+ But in this case, the join requires an extra
+ <literal>DISTINCT</literal> operation and is not more
+ efficient than the subquery.
+ </para>
+ </listitem>
- <para>
- Possible future optimization: MySQL does not rewrite the join
- order for subquery evaluation. In some cases, a subquery could be
- executed more efficiently if MySQL rewrote it as a join. This
- would give the optimizer a chance to choose between more execution
- plans. For example, it could decide whether to read one table or
- the other first.
- </para>
+ <listitem>
+ <para>
+ Possible future optimization: MySQL does not rewrite the join
+ order for subquery evaluation. In some cases, a subquery could
+ be executed more efficiently if MySQL rewrote it as a join.
+ This would give the optimizer a chance to choose between more
+ execution plans. For example, it could decide whether to read
+ one table or the other first.
+ </para>
- <para>
- Example:
- </para>
+ <para>
+ Example:
+ </para>
<programlisting>
SELECT a FROM outer_table AS ot
WHERE a IN (SELECT a FROM inner_table AS it WHERE ot.b = it.b);
</programlisting>
- <para>
- For that query, MySQL always scans <literal>outer_table</literal>
- first and then executes the subquery on
- <literal>inner_table</literal> for each row. If
- <literal>outer_table</literal> has a lot of rows and
- <literal>inner_table</literal> has few rows, the query probably
- will not be as fast as it could be.
- </para>
+ <para>
+ For that query, MySQL always scans
+ <literal>outer_table</literal> first and then executes the
+ subquery on <literal>inner_table</literal> for each row. If
+ <literal>outer_table</literal> has a lot of rows and
+ <literal>inner_table</literal> has few rows, the query
+ probably will not be as fast as it could be.
+ </para>
- <para>
- The preceding query could be rewritten like this:
- </para>
+ <para>
+ The preceding query could be rewritten like this:
+ </para>
<programlisting>
SELECT a FROM outer_table AS ot, inner_table AS it
WHERE ot.a = it.a AND ot.b = it.b;
</programlisting>
- <para>
- In this case, we can scan the small table
- (<literal>inner_table</literal>) and look up rows in
- <literal>outer_table</literal>, which will be fast if there is an
- index on <literal>(ot.a,ot.b)</literal>.
- </para>
+ <para>
+ In this case, we can scan the small table
+ (<literal>inner_table</literal>) and look up rows in
+ <literal>outer_table</literal>, which will be fast if there is
+ an index on <literal>(ot.a,ot.b)</literal>.
+ </para>
+ </listitem>
- <para>
- Possible future optimization: A correlated subquery is evaluated
- for each row of the outer query. A better approach is that if the
- outer row values do not change from the previous row, do not
- evaluate the subquery again. Instead, use its previous result.
- </para>
+ <listitem>
+ <para>
+ Possible future optimization: A correlated subquery is
+ evaluated for each row of the outer query. A better approach
+ is that if the outer row values do not change from the
+ previous row, do not evaluate the subquery again. Instead, use
+ its previous result.
+ </para>
+ </listitem>
- <para>
- Possible future optimization: A subquery in the
- <literal>FROM</literal> clause is evaluated by materializing the
- result into a temporary table, and this table does not use
- indexes. This does not allow the use of indexes in comparison with
- other tables in the query, although that might be useful.
- </para>
+ <listitem>
+ <para>
+ Possible future optimization: A subquery in the
+ <literal>FROM</literal> clause is evaluated by materializing
+ the result into a temporary table, and this table does not use
+ indexes. This does not allow the use of indexes in comparison
+ with other tables in the query, although that might be useful.
+ </para>
+ </listitem>
- <para>
- Possible future optimization: If a subquery in the
- <literal>FROM</literal> clause resembles a view to which the merge
- algorithm can be applied, rewrite the query and apply the merge
- algorithm so that indexes can be used. The following statement
- contains such a subquery:
- </para>
+ <listitem>
+ <para>
+ Possible future optimization: If a subquery in the
+ <literal>FROM</literal> clause resembles a view to which the
+ merge algorithm can be applied, rewrite the query and apply
+ the merge algorithm so that indexes can be used. The following
+ statement contains such a subquery:
+ </para>
<programlisting>
SELECT * FROM (SELECT * FROM t1 WHERE t1.t1_col) AS _t1, t2 WHERE t2.t2_col;
</programlisting>
- <para>
- The statement can be rewritten as a join like this:
- </para>
+ <para>
+ The statement can be rewritten as a join like this:
+ </para>
<programlisting>
SELECT * FROM t1, t2 WHERE t1.t1_col AND t2.t2_col;
</programlisting>
- <para>
- This type of rewriting would provide two benefits:
- </para>
+ <para>
+ This type of rewriting would provide two benefits:
+ </para>
- <itemizedlist>
+ <itemizedlist>
- <listitem>
- <para>
- It avoids the use of a temporary table for which no indexes
- can be used. In the rewritten query, the optimizer can use
- indexes on <literal>t1</literal>.
- </para>
+ <listitem>
+ <para>
+ It avoids the use of a temporary table for which no
+ indexes can be used. In the rewritten query, the optimizer
+ can use indexes on <literal>t1</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ It gives the optimizer more freedom to choose between
+ different execution plans. For example, rewriting the
+ query as a join allows the optimizer to use
+ <literal>t1</literal> or <literal>t2</literal> first.
+ </para>
+ </listitem>
+
+ </itemizedlist>
</listitem>
<listitem>
<para>
- It gives the optimizer more freedom to choose between
- different execution plans. For example, rewriting the query as
- a join allows the optimizer to use <literal>t1</literal> or
- <literal>t2</literal> first.
+ Possible future optimization: For <literal>IN</literal>,
+ <literal>= ANY</literal>, <literal><> ANY</literal>,
+ <literal>= ALL</literal>, and <literal><> ALL</literal>
+ with non-correlated subqueries, use an in-memory hash for a
+ result result or a temporary table with an index for larger
+ results. Example:
</para>
- </listitem>
- </itemizedlist>
-
- <para>
- Possible future optimization: For <literal>IN</literal>,
- <literal>= ANY</literal>, <literal><> ANY</literal>,
- <literal>= ALL</literal>, and <literal><> ALL</literal> with
- non-correlated subqueries, use an in-memory hash for a result
- result or a temporary table with an index for larger results.
- Example:
- </para>
-
<programlisting>
SELECT a FROM big_table AS bt
WHERE non_key_field IN (SELECT non_key_field FROM <replaceable>table</replaceable> WHERE <replaceable>condition</replaceable>)
</programlisting>
- <para>
- In this case, we could create a temporary table:
- </para>
+ <para>
+ In this case, we could create a temporary table:
+ </para>
<programlisting>
CREATE TABLE t (key (non_key_field))
(SELECT non_key_field FROM <replaceable>table</replaceable> WHERE <replaceable>condition</replaceable>)
</programlisting>
- <para>
- Then, for each row in <literal>big_table</literal>, do a key
- lookup in <literal>t</literal> based on
- <literal>bt.non_key_field</literal>.
- </para>
+ <para>
+ Then, for each row in <literal>big_table</literal>, do a key
+ lookup in <literal>t</literal> based on
+ <literal>bt.non_key_field</literal>.
+ </para>
+ </listitem>
+ </itemizedlist>
+
</section>
</appendix>
Modified: trunk/refman-5.0/restrictions.xml
===================================================================
--- trunk/refman-5.0/restrictions.xml 2006-02-07 18:51:11 UTC (rev 1229)
+++ trunk/refman-5.0/restrictions.xml 2006-02-07 19:51:19 UTC (rev 1230)
@@ -349,301 +349,333 @@
<secondary>subqueries</secondary>
</indexterm>
- <para>
- Known bug to be fixed later: If you compare a
- <literal>NULL</literal> value to a subquery using
- <literal>ALL</literal>, <literal>ANY</literal>, or
- <literal>SOME</literal>, and the subquery returns an empty result,
- the comparison might evaluate to the non-standard result of
- <literal>NULL</literal> rather than to <literal>TRUE</literal> or
- <literal>FALSE</literal>.
- </para>
+ <itemizedlist>
- <para>
- A subquery's outer statement can be any one of:
- <literal>SELECT</literal>, <literal>INSERT</literal>,
- <literal>UPDATE</literal>, <literal>DELETE</literal>,
- <literal>SET</literal>, or <literal>DO</literal>.
- </para>
+ <listitem>
+ <para>
+ Known bug to be fixed later: If you compare a
+ <literal>NULL</literal> value to a subquery using
+ <literal>ALL</literal>, <literal>ANY</literal>, or
+ <literal>SOME</literal>, and the subquery returns an empty
+ result, the comparison might evaluate to the non-standard
+ result of <literal>NULL</literal> rather than to
+ <literal>TRUE</literal> or <literal>FALSE</literal>.
+ </para>
+ </listitem>
- <para>
- In general, you cannot modify a table and select from the same
- table in a subquery. For example, this limitation applies to
- statements of the following forms:
- </para>
+ <listitem>
+ <para>
+ A subquery's outer statement can be any one of:
+ <literal>SELECT</literal>, <literal>INSERT</literal>,
+ <literal>UPDATE</literal>, <literal>DELETE</literal>,
+ <literal>SET</literal>, or <literal>DO</literal>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ In general, you cannot modify a table and select from the same
+ table in a subquery. For example, this limitation applies to
+ statements of the following forms:
+ </para>
+
<programlisting>
DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);
</programlisting>
- <para>
- Exception: The preceding prohibition does not apply if you are
- using a subquery for the modified table in the
- <literal>FROM</literal> clause. Example:
- </para>
+ <para>
+ Exception: The preceding prohibition does not apply if you are
+ using a subquery for the modified table in the
+ <literal>FROM</literal> clause. Example:
+ </para>
<programlisting>
UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...) AS _t ...);
</programlisting>
- <para>
- Here the prohibition does not apply because a subquery in the
- <literal>FROM</literal> clause is materialized as a temporary
- table, so the relevant rows in <literal>t</literal> have already
- been selected by the time the update to <literal>t</literal> takes
- place.
- </para>
-
- <para>
- Row comparison operations are only partially supported:
- </para>
-
- <itemizedlist>
-
- <listitem>
<para>
- For <literal><replaceable>expr</replaceable> IN
- (<replaceable>subquery</replaceable>)</literal>,
- <replaceable>expr</replaceable> can be an
- <replaceable>n</replaceable>-tuple (specified via row
- constructor syntax) and the subquery can return rows of
- <replaceable>n</replaceable>-tuples.
+ Here the prohibition does not apply because a subquery in the
+ <literal>FROM</literal> clause is materialized as a temporary
+ table, so the relevant rows in <literal>t</literal> have
+ already been selected by the time the update to
+ <literal>t</literal> takes place.
</para>
</listitem>
<listitem>
<para>
- For <literal><replaceable>expr</replaceable>
- <replaceable>op</replaceable> {ALL|ANY|SOME}
- (<replaceable>subquery</replaceable>)</literal>,
- <replaceable>expr</replaceable> must be a scalar value and the
- subquery must be a column subquery; it cannot return
- multiple-column rows.
+ Row comparison operations are only partially supported:
</para>
- </listitem>
- </itemizedlist>
+ <itemizedlist>
- <para>
- In other words, for a subquery that returns rows of
- <replaceable>n</replaceable>-tuples, this is supported:
- </para>
+ <listitem>
+ <para>
+ For <literal><replaceable>expr</replaceable> IN
+ (<replaceable>subquery</replaceable>)</literal>,
+ <replaceable>expr</replaceable> can be an
+ <replaceable>n</replaceable>-tuple (specified via row
+ constructor syntax) and the subquery can return rows of
+ <replaceable>n</replaceable>-tuples.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ For <literal><replaceable>expr</replaceable>
+ <replaceable>op</replaceable> {ALL|ANY|SOME}
+ (<replaceable>subquery</replaceable>)</literal>,
+ <replaceable>expr</replaceable> must be a scalar value and
+ the subquery must be a column subquery; it cannot return
+ multiple-column rows.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ In other words, for a subquery that returns rows of
+ <replaceable>n</replaceable>-tuples, this is supported:
+ </para>
+
<programlisting>
(<replaceable>val_1</replaceable>, ..., <replaceable>val_n</replaceable>) IN (<replaceable>subquery</replaceable>)
</programlisting>
- <para>
- But this is not supported:
- </para>
+ <para>
+ But this is not supported:
+ </para>
<programlisting>
(<replaceable>val_1</replaceable>, ..., <replaceable>val_n</replaceable>) <replaceable>op</replaceable> {ALL|ANY|SOME} (<replaceable>subquery</replaceable>)
</programlisting>
- <para>
- The reason for supporting row comparisons for
- <literal>IN</literal> but not for the others is that
- <literal>IN</literal> is implemented by rewriting it as a sequence
- of <literal>=</literal> comparisons and <literal>AND</literal>
- operations. This approach cannot be used for
- <literal>ALL</literal>, <literal>ANY</literal>, or
- <literal>SOME</literal>.
- </para>
+ <para>
+ The reason for supporting row comparisons for
+ <literal>IN</literal> but not for the others is that
+ <literal>IN</literal> is implemented by rewriting it as a
+ sequence of <literal>=</literal> comparisons and
+ <literal>AND</literal> operations. This approach cannot be
+ used for <literal>ALL</literal>, <literal>ANY</literal>, or
+ <literal>SOME</literal>.
+ </para>
+ </listitem>
- <para>
- Row constructors are not well optimized. The following two
- expressions are equivalent, but only the second can be optimized:
- </para>
+ <listitem>
+ <para>
+ Row constructors are not well optimized. The following two
+ expressions are equivalent, but only the second can be
+ optimized:
+ </para>
<programlisting>
(col1, col2, ...) = (val1, val2, ...)
col1 = val1 AND col2 = val2 AND ...
</programlisting>
+ </listitem>
- <para>
- Subquery optimization for <literal>IN</literal> is not as
- effective as for the <literal>=</literal> operator.
- </para>
+ <listitem>
+ <para>
+ Subquery optimization for <literal>IN</literal> is not as
+ effective as for the <literal>=</literal> operator.
+ </para>
- <para>
- A typical case for poor <literal>IN</literal> performance is when
- the subquery returns a small number of rows but the outer query
- returns a large number of rows to be compared to the subquery
- result.
- </para>
+ <para>
+ A typical case for poor <literal>IN</literal> performance is
+ when the subquery returns a small number of rows but the outer
+ query returns a large number of rows to be compared to the
+ subquery result.
+ </para>
+ </listitem>
- <para>
- Subqueries in the <literal>FROM</literal> clause cannot be
- correlated subqueries. They are materialized (executed to produce
- a result set) before evaluating the outer query, so they cannot be
- evaluated per row of the outer query.
- </para>
+ <listitem>
+ <para>
+ Subqueries in the <literal>FROM</literal> clause cannot be
+ correlated subqueries. They are materialized (executed to
+ produce a result set) before evaluating the outer query, so
+ they cannot be evaluated per row of the outer query.
+ </para>
+ </listitem>
- <para>
- The optimizer is more mature for joins than for subqueries, so in
- many cases a statement that uses a subquery can be executed more
- efficiently if you rewrite it as a join.
- </para>
+ <listitem>
+ <para>
+ The optimizer is more mature for joins than for subqueries, so
+ in many cases a statement that uses a subquery can be executed
+ more efficiently if you rewrite it as a join.
+ </para>
- <para>
- An exception occurs for the case where an <literal>IN</literal>
- subquery can be rewritten as a <literal>SELECT DISTINCT</literal>
- join. Example:
- </para>
+ <para>
+ An exception occurs for the case where an
+ <literal>IN</literal> subquery can be rewritten as a
+ <literal>SELECT DISTINCT</literal> join. Example:
+ </para>
<programlisting>
SELECT col FROM t1 WHERE id_col IN (SELECT id_col2 FROM t2 WHERE <replaceable>condition</replaceable>);
</programlisting>
- <para>
- That statement can be rewritten as follows:
- </para>
+ <para>
+ That statement can be rewritten as follows:
+ </para>
<programlisting>
SELECT DISTINCT col FROM t1, t2 WHERE t1.id_col = t2.id_col AND <replaceable>condition</replaceable>;
</programlisting>
- <para>
- But in this case, the join requires an extra
- <literal>DISTINCT</literal> operation and is not more efficient
- than the subquery.
- </para>
+ <para>
+ But in this case, the join requires an extra
+ <literal>DISTINCT</literal> operation and is not more
+ efficient than the subquery.
+ </para>
+ </listitem>
- <para>
- Possible future optimization: MySQL does not rewrite the join
- order for subquery evaluation. In some cases, a subquery could be
- executed more efficiently if MySQL rewrote it as a join. This
- would give the optimizer a chance to choose between more execution
- plans. For example, it could decide whether to read one table or
- the other first.
- </para>
+ <listitem>
+ <para>
+ Possible future optimization: MySQL does not rewrite the join
+ order for subquery evaluation. In some cases, a subquery could
+ be executed more efficiently if MySQL rewrote it as a join.
+ This would give the optimizer a chance to choose between more
+ execution plans. For example, it could decide whether to read
+ one table or the other first.
+ </para>
- <para>
- Example:
- </para>
+ <para>
+ Example:
+ </para>
<programlisting>
SELECT a FROM outer_table AS ot
WHERE a IN (SELECT a FROM inner_table AS it WHERE ot.b = it.b);
</programlisting>
- <para>
- For that query, MySQL always scans <literal>outer_table</literal>
- first and then executes the subquery on
- <literal>inner_table</literal> for each row. If
- <literal>outer_table</literal> has a lot of rows and
- <literal>inner_table</literal> has few rows, the query probably
- will not be as fast as it could be.
- </para>
+ <para>
+ For that query, MySQL always scans
+ <literal>outer_table</literal> first and then executes the
+ subquery on <literal>inner_table</literal> for each row. If
+ <literal>outer_table</literal> has a lot of rows and
+ <literal>inner_table</literal> has few rows, the query
+ probably will not be as fast as it could be.
+ </para>
- <para>
- The preceding query could be rewritten like this:
- </para>
+ <para>
+ The preceding query could be rewritten like this:
+ </para>
<programlisting>
SELECT a FROM outer_table AS ot, inner_table AS it
WHERE ot.a = it.a AND ot.b = it.b;
</programlisting>
- <para>
- In this case, we can scan the small table
- (<literal>inner_table</literal>) and look up rows in
- <literal>outer_table</literal>, which will be fast if there is an
- index on <literal>(ot.a,ot.b)</literal>.
- </para>
+ <para>
+ In this case, we can scan the small table
+ (<literal>inner_table</literal>) and look up rows in
+ <literal>outer_table</literal>, which will be fast if there is
+ an index on <literal>(ot.a,ot.b)</literal>.
+ </para>
+ </listitem>
- <para>
- Possible future optimization: A correlated subquery is evaluated
- for each row of the outer query. A better approach is that if the
- outer row values do not change from the previous row, do not
- evaluate the subquery again. Instead, use its previous result.
- </para>
+ <listitem>
+ <para>
+ Possible future optimization: A correlated subquery is
+ evaluated for each row of the outer query. A better approach
+ is that if the outer row values do not change from the
+ previous row, do not evaluate the subquery again. Instead, use
+ its previous result.
+ </para>
+ </listitem>
- <para>
- Possible future optimization: A subquery in the
- <literal>FROM</literal> clause is evaluated by materializing the
- result into a temporary table, and this table does not use
- indexes. This does not allow the use of indexes in comparison with
- other tables in the query, although that might be useful.
- </para>
+ <listitem>
+ <para>
+ Possible future optimization: A subquery in the
+ <literal>FROM</literal> clause is evaluated by materializing
+ the result into a temporary table, and this table does not use
+ indexes. This does not allow the use of indexes in comparison
+ with other tables in the query, although that might be useful.
+ </para>
+ </listitem>
- <para>
- Possible future optimization: If a subquery in the
- <literal>FROM</literal> clause resembles a view to which the merge
- algorithm can be applied, rewrite the query and apply the merge
- algorithm so that indexes can be used. The following statement
- contains such a subquery:
- </para>
+ <listitem>
+ <para>
+ Possible future optimization: If a subquery in the
+ <literal>FROM</literal> clause resembles a view to which the
+ merge algorithm can be applied, rewrite the query and apply
+ the merge algorithm so that indexes can be used. The following
+ statement contains such a subquery:
+ </para>
<programlisting>
SELECT * FROM (SELECT * FROM t1 WHERE t1.t1_col) AS _t1, t2 WHERE t2.t2_col;
</programlisting>
- <para>
- The statement can be rewritten as a join like this:
- </para>
+ <para>
+ The statement can be rewritten as a join like this:
+ </para>
<programlisting>
SELECT * FROM t1, t2 WHERE t1.t1_col AND t2.t2_col;
</programlisting>
- <para>
- This type of rewriting would provide two benefits:
- </para>
+ <para>
+ This type of rewriting would provide two benefits:
+ </para>
- <itemizedlist>
+ <itemizedlist>
- <listitem>
- <para>
- It avoids the use of a temporary table for which no indexes
- can be used. In the rewritten query, the optimizer can use
- indexes on <literal>t1</literal>.
- </para>
+ <listitem>
+ <para>
+ It avoids the use of a temporary table for which no
+ indexes can be used. In the rewritten query, the optimizer
+ can use indexes on <literal>t1</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ It gives the optimizer more freedom to choose between
+ different execution plans. For example, rewriting the
+ query as a join allows the optimizer to use
+ <literal>t1</literal> or <literal>t2</literal> first.
+ </para>
+ </listitem>
+
+ </itemizedlist>
</listitem>
<listitem>
<para>
- It gives the optimizer more freedom to choose between
- different execution plans. For example, rewriting the query as
- a join allows the optimizer to use <literal>t1</literal> or
- <literal>t2</literal> first.
+ Possible future optimization: For <literal>IN</literal>,
+ <literal>= ANY</literal>, <literal><> ANY</literal>,
+ <literal>= ALL</literal>, and <literal><> ALL</literal>
+ with non-correlated subqueries, use an in-memory hash for a
+ result result or a temporary table with an index for larger
+ results. Example:
</para>
- </listitem>
- </itemizedlist>
-
- <para>
- Possible future optimization: For <literal>IN</literal>,
- <literal>= ANY</literal>, <literal><> ANY</literal>,
- <literal>= ALL</literal>, and <literal><> ALL</literal> with
- non-correlated subqueries, use an in-memory hash for a result
- result or a temporary table with an index for larger results.
- Example:
- </para>
-
<programlisting>
SELECT a FROM big_table AS bt
WHERE non_key_field IN (SELECT non_key_field FROM <replaceable>table</replaceable> WHERE <replaceable>condition</replaceable>)
</programlisting>
- <para>
- In this case, we could create a temporary table:
- </para>
+ <para>
+ In this case, we could create a temporary table:
+ </para>
<programlisting>
CREATE TABLE t (key (non_key_field))
(SELECT non_key_field FROM <replaceable>table</replaceable> WHERE <replaceable>condition</replaceable>)
</programlisting>
- <para>
- Then, for each row in <literal>big_table</literal>, do a key
- lookup in <literal>t</literal> based on
- <literal>bt.non_key_field</literal>.
- </para>
+ <para>
+ Then, for each row in <literal>big_table</literal>, do a key
+ lookup in <literal>t</literal> based on
+ <literal>bt.non_key_field</literal>.
+ </para>
+ </listitem>
+ </itemizedlist>
+
</section>
<section id="view-restrictions">
Modified: trunk/refman-5.1/restrictions.xml
===================================================================
--- trunk/refman-5.1/restrictions.xml 2006-02-07 18:51:11 UTC (rev 1229)
+++ trunk/refman-5.1/restrictions.xml 2006-02-07 19:51:19 UTC (rev 1230)
@@ -325,301 +325,333 @@
<secondary>subqueries</secondary>
</indexterm>
- <para>
- Known bug to be fixed later: If you compare a
- <literal>NULL</literal> value to a subquery using
- <literal>ALL</literal>, <literal>ANY</literal>, or
- <literal>SOME</literal>, and the subquery returns an empty result,
- the comparison might evaluate to the non-standard result of
- <literal>NULL</literal> rather than to <literal>TRUE</literal> or
- <literal>FALSE</literal>.
- </para>
+ <itemizedlist>
- <para>
- A subquery's outer statement can be any one of:
- <literal>SELECT</literal>, <literal>INSERT</literal>,
- <literal>UPDATE</literal>, <literal>DELETE</literal>,
- <literal>SET</literal>, or <literal>DO</literal>.
- </para>
+ <listitem>
+ <para>
+ Known bug to be fixed later: If you compare a
+ <literal>NULL</literal> value to a subquery using
+ <literal>ALL</literal>, <literal>ANY</literal>, or
+ <literal>SOME</literal>, and the subquery returns an empty
+ result, the comparison might evaluate to the non-standard
+ result of <literal>NULL</literal> rather than to
+ <literal>TRUE</literal> or <literal>FALSE</literal>.
+ </para>
+ </listitem>
- <para>
- In general, you cannot modify a table and select from the same
- table in a subquery. For example, this limitation applies to
- statements of the following forms:
- </para>
+ <listitem>
+ <para>
+ A subquery's outer statement can be any one of:
+ <literal>SELECT</literal>, <literal>INSERT</literal>,
+ <literal>UPDATE</literal>, <literal>DELETE</literal>,
+ <literal>SET</literal>, or <literal>DO</literal>.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ In general, you cannot modify a table and select from the same
+ table in a subquery. For example, this limitation applies to
+ statements of the following forms:
+ </para>
+
<programlisting>
DELETE FROM t WHERE ... (SELECT ... FROM t ...);
UPDATE t ... WHERE col = (SELECT ... FROM t ...);
{INSERT|REPLACE} INTO t (SELECT ... FROM t ...);
</programlisting>
- <para>
- Exception: The preceding prohibition does not apply if you are
- using a subquery for the modified table in the
- <literal>FROM</literal> clause. Example:
- </para>
+ <para>
+ Exception: The preceding prohibition does not apply if you are
+ using a subquery for the modified table in the
+ <literal>FROM</literal> clause. Example:
+ </para>
<programlisting>
UPDATE t ... WHERE col = (SELECT (SELECT ... FROM t...) AS _t ...);
</programlisting>
- <para>
- Here the prohibition does not apply because a subquery in the
- <literal>FROM</literal> clause is materialized as a temporary
- table, so the relevant rows in <literal>t</literal> have already
- been selected by the time the update to <literal>t</literal> takes
- place.
- </para>
-
- <para>
- Row comparison operations are only partially supported:
- </para>
-
- <itemizedlist>
-
- <listitem>
<para>
- For <literal><replaceable>expr</replaceable> IN
- (<replaceable>subquery</replaceable>)</literal>,
- <replaceable>expr</replaceable> can be an
- <replaceable>n</replaceable>-tuple (specified via row
- constructor syntax) and the subquery can return rows of
- <replaceable>n</replaceable>-tuples.
+ Here the prohibition does not apply because a subquery in the
+ <literal>FROM</literal> clause is materialized as a temporary
+ table, so the relevant rows in <literal>t</literal> have
+ already been selected by the time the update to
+ <literal>t</literal> takes place.
</para>
</listitem>
<listitem>
<para>
- For <literal><replaceable>expr</replaceable>
- <replaceable>op</replaceable> {ALL|ANY|SOME}
- (<replaceable>subquery</replaceable>)</literal>,
- <replaceable>expr</replaceable> must be a scalar value and the
- subquery must be a column subquery; it cannot return
- multiple-column rows.
+ Row comparison operations are only partially supported:
</para>
- </listitem>
- </itemizedlist>
+ <itemizedlist>
- <para>
- In other words, for a subquery that returns rows of
- <replaceable>n</replaceable>-tuples, this is supported:
- </para>
+ <listitem>
+ <para>
+ For <literal><replaceable>expr</replaceable> IN
+ (<replaceable>subquery</replaceable>)</literal>,
+ <replaceable>expr</replaceable> can be an
+ <replaceable>n</replaceable>-tuple (specified via row
+ constructor syntax) and the subquery can return rows of
+ <replaceable>n</replaceable>-tuples.
+ </para>
+ </listitem>
+ <listitem>
+ <para>
+ For <literal><replaceable>expr</replaceable>
+ <replaceable>op</replaceable> {ALL|ANY|SOME}
+ (<replaceable>subquery</replaceable>)</literal>,
+ <replaceable>expr</replaceable> must be a scalar value and
+ the subquery must be a column subquery; it cannot return
+ multiple-column rows.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ In other words, for a subquery that returns rows of
+ <replaceable>n</replaceable>-tuples, this is supported:
+ </para>
+
<programlisting>
(<replaceable>val_1</replaceable>, ..., <replaceable>val_n</replaceable>) IN (<replaceable>subquery</replaceable>)
</programlisting>
- <para>
- But this is not supported:
- </para>
+ <para>
+ But this is not supported:
+ </para>
<programlisting>
(<replaceable>val_1</replaceable>, ..., <replaceable>val_n</replaceable>) <replaceable>op</replaceable> {ALL|ANY|SOME} (<replaceable>subquery</replaceable>)
</programlisting>
- <para>
- The reason for supporting row comparisons for
- <literal>IN</literal> but not for the others is that
- <literal>IN</literal> is implemented by rewriting it as a sequence
- of <literal>=</literal> comparisons and <literal>AND</literal>
- operations. This approach cannot be used for
- <literal>ALL</literal>, <literal>ANY</literal>, or
- <literal>SOME</literal>.
- </para>
+ <para>
+ The reason for supporting row comparisons for
+ <literal>IN</literal> but not for the others is that
+ <literal>IN</literal> is implemented by rewriting it as a
+ sequence of <literal>=</literal> comparisons and
+ <literal>AND</literal> operations. This approach cannot be
+ used for <literal>ALL</literal>, <literal>ANY</literal>, or
+ <literal>SOME</literal>.
+ </para>
+ </listitem>
- <para>
- Row constructors are not well optimized. The following two
- expressions are equivalent, but only the second can be optimized:
- </para>
+ <listitem>
+ <para>
+ Row constructors are not well optimized. The following two
+ expressions are equivalent, but only the second can be
+ optimized:
+ </para>
<programlisting>
(col1, col2, ...) = (val1, val2, ...)
col1 = val1 AND col2 = val2 AND ...
</programlisting>
+ </listitem>
- <para>
- Subquery optimization for <literal>IN</literal> is not as
- effective as for the <literal>=</literal> operator.
- </para>
+ <listitem>
+ <para>
+ Subquery optimization for <literal>IN</literal> is not as
+ effective as for the <literal>=</literal> operator.
+ </para>
- <para>
- A typical case for poor <literal>IN</literal> performance is when
- the subquery returns a small number of rows but the outer query
- returns a large number of rows to be compared to the subquery
- result.
- </para>
+ <para>
+ A typical case for poor <literal>IN</literal> performance is
+ when the subquery returns a small number of rows but the outer
+ query returns a large number of rows to be compared to the
+ subquery result.
+ </para>
+ </listitem>
- <para>
- Subqueries in the <literal>FROM</literal> clause cannot be
- correlated subqueries. They are materialized (executed to produce
- a result set) before evaluating the outer query, so they cannot be
- evaluated per row of the outer query.
- </para>
+ <listitem>
+ <para>
+ Subqueries in the <literal>FROM</literal> clause cannot be
+ correlated subqueries. They are materialized (executed to
+ produce a result set) before evaluating the outer query, so
+ they cannot be evaluated per row of the outer query.
+ </para>
+ </listitem>
- <para>
- The optimizer is more mature for joins than for subqueries, so in
- many cases a statement that uses a subquery can be executed more
- efficiently if you rewrite it as a join.
- </para>
+ <listitem>
+ <para>
+ The optimizer is more mature for joins than for subqueries, so
+ in many cases a statement that uses a subquery can be executed
+ more efficiently if you rewrite it as a join.
+ </para>
- <para>
- An exception occurs for the case where an <literal>IN</literal>
- subquery can be rewritten as a <literal>SELECT DISTINCT</literal>
- join. Example:
- </para>
+ <para>
+ An exception occurs for the case where an
+ <literal>IN</literal> subquery can be rewritten as a
+ <literal>SELECT DISTINCT</literal> join. Example:
+ </para>
<programlisting>
SELECT col FROM t1 WHERE id_col IN (SELECT id_col2 FROM t2 WHERE <replaceable>condition</replaceable>);
</programlisting>
- <para>
- That statement can be rewritten as follows:
- </para>
+ <para>
+ That statement can be rewritten as follows:
+ </para>
<programlisting>
SELECT DISTINCT col FROM t1, t2 WHERE t1.id_col = t2.id_col AND <replaceable>condition</replaceable>;
</programlisting>
- <para>
- But in this case, the join requires an extra
- <literal>DISTINCT</literal> operation and is not more efficient
- than the subquery.
- </para>
+ <para>
+ But in this case, the join requires an extra
+ <literal>DISTINCT</literal> operation and is not more
+ efficient than the subquery.
+ </para>
+ </listitem>
- <para>
- Possible future optimization: MySQL does not rewrite the join
- order for subquery evaluation. In some cases, a subquery could be
- executed more efficiently if MySQL rewrote it as a join. This
- would give the optimizer a chance to choose between more execution
- plans. For example, it could decide whether to read one table or
- the other first.
- </para>
+ <listitem>
+ <para>
+ Possible future optimization: MySQL does not rewrite the join
+ order for subquery evaluation. In some cases, a subquery could
+ be executed more efficiently if MySQL rewrote it as a join.
+ This would give the optimizer a chance to choose between more
+ execution plans. For example, it could decide whether to read
+ one table or the other first.
+ </para>
- <para>
- Example:
- </para>
+ <para>
+ Example:
+ </para>
<programlisting>
SELECT a FROM outer_table AS ot
WHERE a IN (SELECT a FROM inner_table AS it WHERE ot.b = it.b);
</programlisting>
- <para>
- For that query, MySQL always scans <literal>outer_table</literal>
- first and then executes the subquery on
- <literal>inner_table</literal> for each row. If
- <literal>outer_table</literal> has a lot of rows and
- <literal>inner_table</literal> has few rows, the query probably
- will not be as fast as it could be.
- </para>
+ <para>
+ For that query, MySQL always scans
+ <literal>outer_table</literal> first and then executes the
+ subquery on <literal>inner_table</literal> for each row. If
+ <literal>outer_table</literal> has a lot of rows and
+ <literal>inner_table</literal> has few rows, the query
+ probably will not be as fast as it could be.
+ </para>
- <para>
- The preceding query could be rewritten like this:
- </para>
+ <para>
+ The preceding query could be rewritten like this:
+ </para>
<programlisting>
SELECT a FROM outer_table AS ot, inner_table AS it
WHERE ot.a = it.a AND ot.b = it.b;
</programlisting>
- <para>
- In this case, we can scan the small table
- (<literal>inner_table</literal>) and look up rows in
- <literal>outer_table</literal>, which will be fast if there is an
- index on <literal>(ot.a,ot.b)</literal>.
- </para>
+ <para>
+ In this case, we can scan the small table
+ (<literal>inner_table</literal>) and look up rows in
+ <literal>outer_table</literal>, which will be fast if there is
+ an index on <literal>(ot.a,ot.b)</literal>.
+ </para>
+ </listitem>
- <para>
- Possible future optimization: A correlated subquery is evaluated
- for each row of the outer query. A better approach is that if the
- outer row values do not change from the previous row, do not
- evaluate the subquery again. Instead, use its previous result.
- </para>
+ <listitem>
+ <para>
+ Possible future optimization: A correlated subquery is
+ evaluated for each row of the outer query. A better approach
+ is that if the outer row values do not change from the
+ previous row, do not evaluate the subquery again. Instead, use
+ its previous result.
+ </para>
+ </listitem>
- <para>
- Possible future optimization: A subquery in the
- <literal>FROM</literal> clause is evaluated by materializing the
- result into a temporary table, and this table does not use
- indexes. This does not allow the use of indexes in comparison with
- other tables in the query, although that might be useful.
- </para>
+ <listitem>
+ <para>
+ Possible future optimization: A subquery in the
+ <literal>FROM</literal> clause is evaluated by materializing
+ the result into a temporary table, and this table does not use
+ indexes. This does not allow the use of indexes in comparison
+ with other tables in the query, although that might be useful.
+ </para>
+ </listitem>
- <para>
- Possible future optimization: If a subquery in the
- <literal>FROM</literal> clause resembles a view to which the merge
- algorithm can be applied, rewrite the query and apply the merge
- algorithm so that indexes can be used. The following statement
- contains such a subquery:
- </para>
+ <listitem>
+ <para>
+ Possible future optimization: If a subquery in the
+ <literal>FROM</literal> clause resembles a view to which the
+ merge algorithm can be applied, rewrite the query and apply
+ the merge algorithm so that indexes can be used. The following
+ statement contains such a subquery:
+ </para>
<programlisting>
SELECT * FROM (SELECT * FROM t1 WHERE t1.t1_col) AS _t1, t2 WHERE t2.t2_col;
</programlisting>
- <para>
- The statement can be rewritten as a join like this:
- </para>
+ <para>
+ The statement can be rewritten as a join like this:
+ </para>
<programlisting>
SELECT * FROM t1, t2 WHERE t1.t1_col AND t2.t2_col;
</programlisting>
- <para>
- This type of rewriting would provide two benefits:
- </para>
+ <para>
+ This type of rewriting would provide two benefits:
+ </para>
- <itemizedlist>
+ <itemizedlist>
- <listitem>
- <para>
- It avoids the use of a temporary table for which no indexes
- can be used. In the rewritten query, the optimizer can use
- indexes on <literal>t1</literal>.
- </para>
+ <listitem>
+ <para>
+ It avoids the use of a temporary table for which no
+ indexes can be used. In the rewritten query, the optimizer
+ can use indexes on <literal>t1</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ It gives the optimizer more freedom to choose between
+ different execution plans. For example, rewriting the
+ query as a join allows the optimizer to use
+ <literal>t1</literal> or <literal>t2</literal> first.
+ </para>
+ </listitem>
+
+ </itemizedlist>
</listitem>
<listitem>
<para>
- It gives the optimizer more freedom to choose between
- different execution plans. For example, rewriting the query as
- a join allows the optimizer to use <literal>t1</literal> or
- <literal>t2</literal> first.
+ Possible future optimization: For <literal>IN</literal>,
+ <literal>= ANY</literal>, <literal><> ANY</literal>,
+ <literal>= ALL</literal>, and <literal><> ALL</literal>
+ with non-correlated subqueries, use an in-memory hash for a
+ result result or a temporary table with an index for larger
+ results. Example:
</para>
- </listitem>
- </itemizedlist>
-
- <para>
- Possible future optimization: For <literal>IN</literal>,
- <literal>= ANY</literal>, <literal><> ANY</literal>,
- <literal>= ALL</literal>, and <literal><> ALL</literal> with
- non-correlated subqueries, use an in-memory hash for a result
- result or a temporary table with an index for larger results.
- Example:
- </para>
-
<programlisting>
SELECT a FROM big_table AS bt
WHERE non_key_field IN (SELECT non_key_field FROM <replaceable>table</replaceable> WHERE <replaceable>condition</replaceable>)
</programlisting>
- <para>
- In this case, we could create a temporary table:
- </para>
+ <para>
+ In this case, we could create a temporary table:
+ </para>
<programlisting>
CREATE TABLE t (key (non_key_field))
(SELECT non_key_field FROM <replaceable>table</replaceable> WHERE <replaceable>condition</replaceable>)
</programlisting>
- <para>
- Then, for each row in <literal>big_table</literal>, do a key
- lookup in <literal>t</literal> based on
- <literal>bt.non_key_field</literal>.
- </para>
+ <para>
+ Then, for each row in <literal>big_table</literal>, do a key
+ lookup in <literal>t</literal> based on
+ <literal>bt.non_key_field</literal>.
+ </para>
+ </listitem>
+ </itemizedlist>
+
</section>
<section id="view-restrictions">
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r1230 - in trunk: . refman-4.1 refman-5.0 refman-5.1 | paul | 7 Feb |
