Author: paul
Date: 2007-12-12 20:46:41 +0100 (Wed, 12 Dec 2007)
New Revision: 9270
Log:
r34203@arctic: paul | 2007-12-12 13:40:37 -0600
Fix ID references.
Sync translations.
Modified:
trunk/dynamic-docs/changelog/mysqld.xml
trunk/it/refman-5.1/mysql-cluster-configuration.xml
trunk/it/refman-5.1/optimization.xml
trunk/it/refman-5.1/partitioning-guide.xml
trunk/it/refman-5.1/partitioning.xml
trunk/pt/refman-5.1/mysql-cluster-configuration.xml
trunk/pt/refman-5.1/optimization.xml
trunk/pt/refman-5.1/partitioning-guide.xml
trunk/pt/refman-5.1/partitioning.xml
trunk/refman-5.1/renamed-nodes.txt
trunk/refman-5.2/renamed-nodes.txt
trunk/refman-6.0/Makefile.depends
Property changes on: trunk
___________________________________________________________________
Name: svk:merge
- 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:34153
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:28078
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:22582
+ 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:34203
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:28078
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:22582
Modified: trunk/dynamic-docs/changelog/mysqld.xml
===================================================================
--- trunk/dynamic-docs/changelog/mysqld.xml 2007-12-12 19:26:14 UTC (rev 9269)
+++ trunk/dynamic-docs/changelog/mysqld.xml 2007-12-12 19:46:41 UTC (rev 9270)
Changed blocks: 2, Lines Added: 1, Lines Deleted: 28; 1217 bytes
@@ -113873,7 +113873,7 @@
</para>
<para>
You can find
- <link linkend="partitioning-limitations-functions-disallowed">a
+ <link linkend="partitioning-limitations-functions">a
complete list of these functions</link> under
<citetitle>Partitioning Limitations</citetitle>.
</para>
@@ -127580,33 +127580,6 @@
</logentry>
- <logentry entrytype="feature">
-
- <tags>
- <manual type="partitioning"/>
- </tags>
-
- <versions>
- <version ver="5.1.12"/>
- </versions>
-
- <message>
-
- <para>
- A number of MySQL functions.
- </para>
-
- <para>
- You can find
- <link linkend="partitioning-limitations-functions-disallowed">a
- complete list of these functions</link> under
- <citetitle>Partitioning Limitations</citetitle>.
- </para>
-
- </message>
-
- </logentry>
-
<logentry entrytype="bug">
<tags>
Modified: trunk/it/refman-5.1/mysql-cluster-configuration.xml
===================================================================
--- trunk/it/refman-5.1/mysql-cluster-configuration.xml 2007-12-12 19:26:14 UTC (rev 9269)
+++ trunk/it/refman-5.1/mysql-cluster-configuration.xml 2007-12-12 19:46:41 UTC (rev 9270)
Changed blocks: 1, Lines Added: 30, Lines Deleted: 24; 3630 bytes
@@ -1927,41 +1927,47 @@
</para>
<para>
- For each active transaction in the cluster there must be a
- record in one of the cluster nodes. The task of coordinating
- transactions is spread among the nodes. The total number of
- transaction records in the cluster is the number of
- transactions in any given node times the number of nodes in
- the cluster.
+ Each cluster data node requires a transaction record for
+ each active transaction in the cluster. The task of
+ coordinating transactions is distributed among all of the
+ data nodes. The total number of transaction records in the
+ cluster is the number of transactions in any given node
+ times the number of nodes in the cluster.
</para>
<para>
Transaction records are allocated to individual MySQL
- servers. Normally, there is at least one transaction record
- allocated per connection that using any table in the
- cluster. For this reason, one should ensure that there are
- more transaction records in the cluster than there are
- concurrent connections to all MySQL servers in the cluster.
+ servers. Each connection to a MySQL server requires at least
+ one transaction record, plus an additional transaction
+ object per table accessed by that connection. This means
+ that a reasonable minimum for this parameter is
+
+<programlisting>
+MaxNoOfConcurrentTransactions =
+ (maximum number of tables accessed in any single transaction + 1)
+ * number of cluster SQL nodes
+</programlisting>
+
+ For example, suppose that there are 4 SQL nodes using the
+ cluster. A single join involving 5 tables requires 6
+ transaction records; if there are 5 such joins in a
+ transaction, then 5 * 6 = 30 transaction records are
+ required for this transaction, per MySQL server, or 30 * 4 =
+ 120 transaction records total.
</para>
<para>
This parameter must be set to the same value for all cluster
- nodes.
+ data nodes. This is due to the fact that, when a data node
+ fails, the oldest surviving node re-creates the transaction
+ state of all transactions that were ongoing in the failed
+ node.
</para>
- <remark role="todo">
- [js] What does the following really mean? If we shouldn't
- change this, then why is there an option to do so?
- </remark>
-
<para>
- Changing this parameter is never safe and doing so can cause
- a cluster to crash. When a node crashes, one of the nodes
- (actually the oldest surviving node) will build up the
- transaction state of all transactions ongoing in the crashed
- node at the time of the crash. It is thus important that
- this node has as many transaction records as the failed
- node.
+ Changing the value of
+ <literal>MaxNoOfConcurrentTransactions</literal> requires a
+ complete shutdown and restart of the cluster.
</para>
<para>
Modified: trunk/it/refman-5.1/optimization.xml
===================================================================
--- trunk/it/refman-5.1/optimization.xml 2007-12-12 19:26:14 UTC (rev 9269)
+++ trunk/it/refman-5.1/optimization.xml 2007-12-12 19:46:41 UTC (rev 9270)
Changed blocks: 39, Lines Added: 961, Lines Deleted: 137; 58404 bytes
@@ -228,12 +228,10 @@
feeling for each SQL server's bottlenecks. For example, MySQL is
very fast in retrieving and updating rows for
<literal>MyISAM</literal> tables, but has a problem in mixing
- slow readers and writers on the same table. Oracle, on the other
- hand, has a big problem when you try to access rows that you
- have recently updated (until they are flushed to disk).
- Transactional database systems in general are not very good at
- generating summary tables from log tables, because in this case
- row locking is almost useless.
+ slow readers and writers on the same table. Transactional
+ database systems in general are not very good at generating
+ summary tables from log tables, because in this case row locking
+ is almost useless.
</para>
<formalpara role="mnmas">
@@ -645,7 +643,7 @@
<para>
If your problem is with a specific MySQL expression or function,
you can perform a timing test by invoking the
- <literal>BENCHMARK()</literal> function using the
+ <function role="sql">BENCHMARK()</function> function using the
<command>mysql</command> client program. Its syntax is
<literal>BENCHMARK(<replaceable>loop_count</replaceable>,<replaceable>expression</replaceable>)</literal>.
The return value is always zero, but <command>mysql</command>
@@ -671,9 +669,9 @@
<para>
All MySQL functions should be highly optimized, but there may be
- some exceptions. <literal>BENCHMARK()</literal> is an excellent
- tool for finding out if some function is a problem for your
- queries.
+ some exceptions. <function role="sql">BENCHMARK()</function> is an
+ excellent tool for finding out if some function is a problem for
+ your queries.
</para>
<section id="explain">
@@ -1197,13 +1195,16 @@
<para>
<literal>range</literal> can be used when a key column
is compared to a constant using any of the
- <literal>=</literal>, <literal><></literal>,
- <literal>></literal>, <literal>>=</literal>,
- <literal><</literal>, <literal><=</literal>,
- <literal>IS NULL</literal>,
- <literal><=></literal>,
- <literal>BETWEEN</literal>, or <literal>IN</literal>
- operators:
+ <function role="sqlop" condition="operator_equal">=</function>,
+ <function role="sqlop" condition="operator_not-equal"><></function>,
+ <function role="sqlop" condition="operator_greater-than">></function>,
+ <function role="sqlop" condition="operator_greater-than-or-equal">>=</function>,
+ <function role="sqlop" condition="operator_less-than"><</function>,
+ <function role="sqlop" condition="operator_less-than-or-equal"><=</function>,
+ <function role="sqlop">IS NULL</function>,
+ <function role="sqlop" condition="operator_equal-to"><=></function>,
+ <function role="sqlop">BETWEEN</function>, or
+ <function role="sql">IN()</function> operators:
</para>
<programlisting>
@@ -1525,13 +1526,31 @@
<listitem>
<para>
+ <literal>Scanned <replaceable>N</replaceable>
+ databases</literal>
+ </para>
+
+ <para>
+ This indicates how many directory scans the server
+ performs when processing a query for
+ <literal>INFORMATION_SCHEMA</literal> tables, as
+ described in
+ <xref linkend="information-schema-optimization"/>. The
+ value of <replaceable>N</replaceable> can be 0, 1, or
+ <literal>all</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
<literal>Select tables optimized away</literal>
</para>
<para>
The query contained only aggregate functions
- (<literal>MIN()</literal>, <literal>MAX()</literal>)
- that were all resolved using an index, or
+ (<function role="sql">MIN()</function>,
+ <function role="sql">MAX()</function>) that were all
+ resolved using an index, or
<function role="sql">COUNT(*)</function> for
<literal>MyISAM</literal>, and no <literal>GROUP
BY</literal> clause. The optimizer determined that only
@@ -1541,6 +1560,60 @@
<listitem>
<para>
+ <literal>Skip_open_table</literal>,
+ <literal>Open_frm_only</literal>,
+ <literal>Open_trigger_only</literal>,
+ <literal>Open_full_table</literal>
+ </para>
+
+ <para>
+ These values indicate file-opening optimizations that
+ apply to queries for
+ <literal>INFORMATION_SCHEMA</literal> tables, as
+ described in
+ <xref linkend="information-schema-optimization"/>.
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>Skip_open_table</literal>: Table files do
+ not need to be opened. The information has already
+ become available within the query by scanning the
+ database directory.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>Open_frm_only</literal>: Only the table's
+ <filename>.frm</filename> file need be opened.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>Open_trigger_only</literal>: Only the
+ table's <filename>.TRG</filename> file need be
+ opened.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>Open_full_table</literal>: The unoptimized
+ information lookup. The <filename>.frm</filename>,
+ <filename>.MYD</filename>, and
+ <filename>.MYI</filename> files must be opened.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </listitem>
+
+ <listitem>
+ <para>
<literal>Using filesort</literal>
</para>
@@ -2338,7 +2411,7 @@
<literal>WHERE</literal> if you do not use <literal>GROUP
BY</literal> or aggregate functions
(<function role="sql">COUNT()</function>,
- <literal>MIN()</literal>, and so on).
+ <function role="sql">MIN()</function>, and so on).
</para>
</listitem>
@@ -2540,9 +2613,11 @@
For both <literal>BTREE</literal> and
<literal>HASH</literal> indexes, comparison of a key part
with a constant value is a range condition when using the
- <literal>=</literal>, <literal><=></literal>,
- <literal>IN</literal>, <literal>IS NULL</literal>, or
- <literal>IS NOT NULL</literal> operators.
+ <function role="sqlop" condition="operator_equal">=</function>,
+ <function role="sqlop" condition="operator_equal-to"><=></function>,
+ <function role="sql">IN()</function>,
+ <function role="sqlop">IS NULL</function>, or
+ <function role="sqlop">IS NOT NULL</function> operators.
</para>
</listitem>
@@ -2550,21 +2625,28 @@
<para>
For <literal>BTREE</literal> indexes, comparison of a key
part with a constant value is a range condition when using
- the <literal>></literal>, <literal><</literal>,
- <literal>>=</literal>, <literal><=</literal>,
- <literal>BETWEEN</literal>, <literal>!=</literal>, or
- <literal><></literal> operators, or <literal>LIKE
- '<replaceable>pattern</replaceable>'</literal> (where
- <literal>'<replaceable>pattern</replaceable>'</literal>
- does not start with a wildcard).
+ the
+ <function role="sqlop" condition="operator_greater-than">></function>,
+ <function role="sqlop" condition="operator_less-than"><</function>,
+ <function role="sqlop" condition="operator_greater-than-or-equal">>=</function>,
+ <function role="sqlop" condition="operator_less-than-or-equal"><=</function>,
+ <function role="sqlop">BETWEEN</function>,
+ <function role="sqlop" condition="operator_not-equal">!=</function>,
+ or
+ <function role="sqlop" condition="operator_not-equal"><></function>
+ operators, or <function role="sqlop">LIKE</function>
+ comparisons if the argument to
+ <function role="sqlop">LIKE</function> is a constant
+ string that does not start with a wildcard character.
</para>
</listitem>
<listitem>
<para>
For all types of indexes, multiple range conditions
- combined with <literal>OR</literal> or
- <literal>AND</literal> form a range condition.
+ combined with <function role="sqlop">OR</function> or
+ <function role="sqlop">AND</function> form a range
+ condition.
</para>
</listitem>
@@ -2751,9 +2833,10 @@
<para>
The range condition extraction algorithm can handle nested
- <literal>AND</literal>/<literal>OR</literal> constructs of
- arbitrary depth, and its output does not depend on the order
- in which conditions appear in <literal>WHERE</literal> clause.
+ <function role="sqlop">AND</function>/<function role="sqlop">OR</function>
+ constructs of arbitrary depth, and its output does not depend
+ on the order in which conditions appear in
+ <literal>WHERE</literal> clause.
</para>
<para>
@@ -2846,13 +2929,14 @@
Here, <replaceable>const1</replaceable>,
<replaceable>const2</replaceable>, … are constants,
<replaceable>cmp</replaceable> is one of the
- <literal>=</literal>, <literal><=></literal>, or
- <literal>IS NULL</literal> comparison operators, and the
- conditions cover all index parts. (That is, there are
- <replaceable>N</replaceable> conditions, one for each part
- of an <replaceable>N</replaceable>-part index.) For
- example, the following is a range condition for a
- three-part <literal>HASH</literal> index:
+ <function role="sqlop" condition="operator_equal">=</function>,
+ <function role="sqlop" condition="operator_equal-to"><=></function>,
+ or <function role="sqlop">IS NULL</function> comparison
+ operators, and the conditions cover all index parts. (That
+ is, there are <replaceable>N</replaceable> conditions, one
+ for each part of an <replaceable>N</replaceable>-part
+ index.) For example, the following is a range condition
+ for a three-part <literal>HASH</literal> index:
</para>
<programlisting>
@@ -2869,21 +2953,29 @@
<para>
For a <literal>BTREE</literal> index, an interval might be
usable for conditions combined with
- <literal>AND</literal>, where each condition compares a
- key part with a constant value using <literal>=</literal>,
- <literal><=></literal>, <literal>IS NULL</literal>,
- <literal>></literal>, <literal><</literal>,
- <literal>>=</literal>, <literal><=</literal>,
- <literal>!=</literal>, <literal><></literal>,
- <literal>BETWEEN</literal>, or <literal>LIKE
- '<replaceable>pattern</replaceable>'</literal> (where
+ <function role="sqlop">AND</function>, where each
+ condition compares a key part with a constant value using
+ <function role="sqlop" condition="operator_equal">=</function>,
+ <function role="sqlop" condition="operator_equal-to"><=></function>,
+ <function role="sqlop">IS NULL</function>,
+ <function role="sqlop" condition="operator_greater-than">></function>,
+ <function role="sqlop" condition="operator_less-than"><</function>,
+ <function role="sqlop" condition="operator_greater-than-or-equal">>=</function>,
+ <function role="sqlop" condition="operator_less-than-or-equal"><=</function>,
+ <function role="sqlop" condition="operator_not-equal">!=</function>,
+ <function role="sqlop" condition="operator_not-equal"><></function>,
+ <function role="sqlop">BETWEEN</function>, or
+ <function role="sqlop" condition="operator_like">LIKE
+ '<replaceable>pattern</replaceable>'</function> (where
<literal>'<replaceable>pattern</replaceable>'</literal>
does not start with a wildcard). An interval can be used
as long as it is possible to determine a single key tuple
containing all rows that match the condition (or two
- intervals if <literal><></literal> or
- <literal>!=</literal> is used). For example, for this
- condition:
+ intervals if
+ <function role="sqlop" condition="operator_not-equal"><></function>
+ or
+ <function role="sqlop" condition="operator_not-equal">!=</function>
+ is used). For example, for this condition:
</para>
<programlisting>
@@ -2910,11 +3002,12 @@
<listitem>
<para>
If conditions that cover sets of rows contained within
- intervals are combined with <literal>OR</literal>, they
- form a condition that covers a set of rows contained
- within the union of their intervals. If the conditions are
- combined with <literal>AND</literal>, they form a
+ intervals are combined with
+ <function role="sqlop">OR</function>, they form a
condition that covers a set of rows contained within the
+ union of their intervals. If the conditions are combined
+ with <function role="sqlop">AND</function>, they form a
+ condition that covers a set of rows contained within the
intersection of their intervals. For example, for this
condition on a two-part index:
</para>
@@ -3133,7 +3226,8 @@
<listitem>
<para>
If your query has a complex <literal>WHERE</literal> clause
- with deep <literal>AND</literal>/<literal>OR</literal>
+ with deep
+ <function role="sqlop">AND</function>/<function role="sqlop">OR</function>
nesting and MySQL doesn't choose the optimal plan, try
distributing terms using the following identity laws:
</para>
@@ -3167,8 +3261,8 @@
This access algorithm can be employed when a
<literal>WHERE</literal> clause was converted to several range
conditions on different keys combined with
- <literal>AND</literal>, and each condition is one of the
- following:
+ <function role="sqlop">AND</function>, and each condition is
+ one of the following:
</para>
<itemizedlist>
@@ -3248,8 +3342,8 @@
algorithm can be employed when the table's
<literal>WHERE</literal> clause was converted to several range
conditions on different keys combined with
- <literal>OR</literal>, and each condition is one of the
- following:
+ <function role="sqlop">OR</function>, and each condition is
+ one of the following:
</para>
<itemizedlist>
@@ -3302,8 +3396,9 @@
<para>
This access algorithm is employed when the
<literal>WHERE</literal> clause was converted to several range
- conditions combined by <literal>OR</literal>, but for which
- the Index Merge method union algorithm is not applicable.
+ conditions combined by <function role="sqlop">OR</function>,
+ but for which the Index Merge method union algorithm is not
+ applicable.
</para>
<para>
@@ -3329,7 +3424,7 @@
<section id="is-null-optimization">
- <title><literal>IS NULL</literal> Optimization</title>
+ <title><function role="sqlop">IS NULL</function> Optimization</title>
<indexterm>
<primary>IS NULL</primary>
@@ -3341,11 +3436,12 @@
<para>
MySQL can perform the same optimization on
- <replaceable>col_name</replaceable> <literal>IS NULL</literal>
- that it can use for <replaceable>col_name</replaceable>
- <literal>=</literal> <replaceable>constant_value</replaceable>.
- For example, MySQL can use indexes and ranges to search for
- <literal>NULL</literal> with <literal>IS NULL</literal>.
+ <replaceable>col_name</replaceable> <function role="sqlop">IS
+ NULL</function> that it can use for
+ <replaceable>col_name</replaceable> <literal>=</literal>
+ <replaceable>constant_value</replaceable>. For example, MySQL
+ can use indexes and ranges to search for <literal>NULL</literal>
+ with <function role="sqlop">IS NULL</function>.
</para>
<para>
@@ -3363,10 +3459,10 @@
<para>
If a <literal>WHERE</literal> clause includes a
- <replaceable>col_name</replaceable> <literal>IS NULL</literal>
- condition for a column that is declared as <literal>NOT
- NULL</literal>, that expression is optimized away. This
- optimization does not occur in cases when the column might
+ <replaceable>col_name</replaceable> <function role="sqlop">IS
+ NULL</function> condition for a column that is declared as
+ <literal>NOT NULL</literal>, that expression is optimized away.
+ This optimization does not occur in cases when the column might
produce <literal>NULL</literal> anyway; for example, if it comes
from a table on the right side of a <literal>LEFT
JOIN</literal>.
@@ -3383,8 +3479,8 @@
</para>
<para>
- This optimization can handle one <literal>IS NULL</literal> for
- any key part.
+ This optimization can handle one <function role="sqlop">IS
+ NULL</function> for any key part.
</para>
<para>
@@ -3416,11 +3512,11 @@
</para>
<para>
- Note that the optimization can handle only one <literal>IS
- NULL</literal> level. In the following query, MySQL uses key
- lookups only on the expression <literal>(t1.a=t2.a AND t2.a IS
- NULL)</literal> and is not able to use the key part on
- <literal>b</literal>:
+ Note that the optimization can handle only one
+ <function role="sqlop">IS NULL</function> level. In the
+ following query, MySQL uses key lookups only on the expression
+ <literal>(t1.a=t2.a AND t2.a IS NULL)</literal> and is not able
+ to use the key part on <literal>b</literal>:
</para>
<programlisting>
@@ -4938,8 +5034,9 @@
<listitem>
<para>
The only aggregate functions used (if any) are
- <literal>MIN()</literal> and <literal>MAX()</literal>, and
- all of them refer to the same column.
+ <function role="sql">MIN()</function> and
+ <function role="sql">MAX()</function>, and all of them
+ refer to the same column.
</para>
</listitem>
@@ -4949,8 +5046,8 @@
<literal>GROUP BY</literal> referenced in the query must
be constants (that is, they must be referenced in
equalities with constants), except for the argument of
- <literal>MIN()</literal> or <literal>MAX()</literal>
- functions.
+ <function role="sql">MIN()</function> or
+ <function role="sql">MAX()</function> functions.
</para>
</listitem>
@@ -4988,8 +5085,8 @@
<listitem>
<para>
There are aggregate functions other than
- <literal>MIN()</literal> or <literal>MAX()</literal>, for
- example:
+ <function role="sql">MIN()</function> or
+ <function role="sql">MAX()</function>, for example:
</para>
<programlisting>
@@ -5284,8 +5381,8 @@
<para>
You do not need to distinguish <literal>NULL</literal> from
<literal>FALSE</literal> subquery results. (If the subquery
- is a part of an <literal>OR</literal> or
- <literal>AND</literal> expression in the
+ is a part of an <function role="sqlop">OR</function> or
+ <function role="sqlop">AND</function> expression in the
<literal>WHERE</literal> clause, MySQL assumes that you
don't care.)
</para>
@@ -5344,9 +5441,9 @@
</programlisting>
<para>
- The need to evaluate the extra <literal>IS NULL</literal>
- condition is why MySQL has the <literal>ref_or_null</literal>
- access method:
+ The need to evaluate the extra <function role="sqlop">IS
+ NULL</function> condition is why MySQL has the
+ <literal>ref_or_null</literal> access method:
</para>
<programlisting>
@@ -5737,8 +5834,8 @@
<para>
Then <literal>NULL IN (SELECT ...)</literal> will never be
evaluated because MySQL stops evaluating
- <literal>AND</literal> parts as soon as the expression
- result is clear.
+ <function role="sqlop">AND</function> parts as soon as the
+ expression result is clear.
</para>
</listitem>
@@ -5967,6 +6064,724 @@
</section>
+ <section id="information-schema-optimization">
+
+ <title><literal>INFORMATION_SCHEMA</literal> Optimization</title>
+
+ <para>
+ In MySQL 5.1.23, changes to the implementation of
+ <literal>INFORMATION_SCHEMA</literal> were made that optimize
+ certain types of queries for
+ <literal>INFORMATION_SCHEMA</literal> tables so that they
+ execute more quickly. This section provides guidelines on
+ writing queries that take advantage of these optimizations. In
+ general, the strategies outlined here minimize the need for the
+ server to access the filesystem to obtain the information that
+ makes up the contents of <literal>INFORMATION_SCHEMA</literal>
+ tables. By writing queries that enable the server to avoid
+ directory scans or opening table files, you will obtain better
+ performance.
+ </para>
+
+ <para>
+ <emphasis role="bold">1) Try to use constant lookup values for
+ database and table names in the <literal>WHERE</literal>
+ clause</emphasis>
+ </para>
+
+ <para>
+ You can take advantage of this principle as follows:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ To look up databases or tables, use expressions that
+ evaluate to a constant, such as literal values, functions
+ that return a constant, or scalar subqueries.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Avoid queries that use a non-constant database name lookup
+ value (or no lookup value) because they require a scan of
+ the data directory to find matching database directory
+ names.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Within a database, avoid queries that use a non-constant
+ table name lookup value (or no lookup value) because they
+ require a scan of the database directory to find matching
+ table files.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ This principle applies to the
+ <literal>INFORMATION_SCHEMA</literal> tables shown in the
+ following table, which shows the columns for which a constant
+ lookup value enables the server to avoid a directory scan. For
+ example, if you are selecting from <literal>TABLES</literal>,
+ using a constant lookup value for
+ <literal>TABLE_SCHEMA</literal> in the <literal>WHERE</literal>
+ clause enables a data directory scan to be avoided.
+ </para>
+
+ <informaltable>
+ <tgroup cols="3">
+ <colspec colwidth="34*"/>
+ <colspec colwidth="33*"/>
+ <colspec colwidth="33*"/>
+ <tbody>
+ <row>
+ <entry><emphasis role="bold">Table</emphasis></entry>
+ <entry><emphasis role="bold">Column to specify to avoid data directory
+ scan</emphasis></entry>
+ <entry><emphasis role="bold">Column to specify to avoid database directory
+ scan</emphasis></entry>
+ </row>
+ <row>
+ <entry><literal>COLUMNS</literal></entry>
+ <entry><literal>TABLE_SCHEMA</literal></entry>
+ <entry><literal>TABLE_NAME</literal></entry>
+ </row>
+ <row>
+ <entry><literal>KEY_COLUMN_USAGE</literal></entry>
+ <entry><literal>TABLE_SCHEMA</literal></entry>
+ <entry><literal>TABLE_NAME</literal></entry>
+ </row>
+ <row>
+ <entry><literal>PARTITIONS</literal></entry>
+ <entry><literal>TABLE_SCHEMA</literal></entry>
+ <entry><literal>TABLE_NAME</literal></entry>
+ </row>
+ <row>
+ <entry><literal>REFERENTIAL_CONSTRAINTS</literal></entry>
+ <entry><literal>CONSTRAINT_SCHEMA</literal></entry>
+ <entry><literal>TABLE_NAME</literal></entry>
+ </row>
+ <row>
+ <entry><literal>STATISTICS</literal></entry>
+ <entry><literal>TABLE_SCHEMA</literal></entry>
+ <entry><literal>TABLE_NAME</literal></entry>
+ </row>
+ <row>
+ <entry><literal>TABLES</literal></entry>
+ <entry><literal>TABLE_SCHEMA</literal></entry>
+ <entry><literal>TABLE_NAME</literal></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_CONSTRAINTS</literal></entry>
+ <entry><literal>TABLE_SCHEMA</literal></entry>
+ <entry><literal>TABLE_NAME</literal></entry>
+ </row>
+ <row>
+ <entry><literal>TRIGGERS</literal></entry>
+ <entry><literal>EVENT_OBJECT_SCHEMA</literal></entry>
+ <entry><literal>EVENT_OBJECT_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>VIEWS</literal></entry>
+ <entry><literal>TABLE_SCHEMA</literal></entry>
+ <entry><literal>TABLE_NAME</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </informaltable>
+
+ <para>
+ The benefit of a query that is limited to a specific constant
+ database name is that checks need be made only for the named
+ database directories. Example:
+ </para>
+
+<programlisting>SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
+WHERE TABLE_SCHEMA = 'test';
+</programlisting>
+
+ <para>
+ Use of the literal database name <literal>test</literal> enables
+ the server to check only the <literal>test</literal> database
+ directory, regardless of how many databases there might be. By
+ contrast, the following query is less efficient because it
+ requires a scan of the data directory to determine which
+ database names match the pattern <literal>'test%'</literal>:
+ </para>
+
+<programlisting>SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
+WHERE TABLE_SCHEMA LIKE 'test%';
+</programlisting>
+
+ <para>
+ For a query that is limited to a specific constant table name,
+ checks need be made only for the named tables within the
+ corresponding database directory. Example:
+ </para>
+
+<programlisting>SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
+WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
+</programlisting>
+
+ <para>
+ Use of the literal table name <literal>t1</literal> enables the
+ server to check only the files for the <literal>t1</literal>
+ table, regardless of how many tables there might be in the
+ <literal>test</literal> database. By contrast, the following
+ query requires a scan of the <literal>test</literal> database
+ directory to determine which table names match the pattern
+ <literal>'t%'</literal>:
+ </para>
+
+<programlisting>SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
+WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME LIKE 't%';
+</programlisting>
+
+ <para>
+ The following query requires a scan of the database directory to
+ determine matching database names for the pattern
+ <literal>'test%'</literal>, and for each matching database, it
+ requires a scan of the database directory to determine matching
+ table names for the pattern <literal>'t%'</literal>:
+ </para>
+
+<programlisting>SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
+WHERE TABLE_SCHEMA = 'test%' AND TABLE_NAME LIKE 't%';
+</programlisting>
+
+ <para>
+ <emphasis role="bold">2) Write queries that minimize the number
+ of table files that must be opened</emphasis>
+ </para>
+
+ <para>
+ For queries that refer to certain
+ <literal>INFORMATION_SCHEMA</literal> table columns, several
+ optimizations are available that minimize the number of table
+ files that must be opened. Example:
+ </para>
+
+<programlisting>SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES
+WHERE TABLE_SCHEMA = 'test';
+</programlisting>
+
+ <para>
+ In this case, after the server has scanned the database
+ directory to determine the names of the tables in the database,
+ those names become available with no further filesystem lookups.
+ Thus, <literal>TABLE_NAME</literal> requires no files to be
+ opened. The <literal>ENGINE</literal> (storage engine) value can
+ be determined by opening the table's <filename>.frm</filename>
+ file, without touching other table files such as the
+ <filename>.MYD</filename> or <filename>.MYI</filename> file.
+ </para>
+
+ <para>
+ Some values, such as <literal>INDEX_LENGTH</literal> for
+ <literal>MyISAM</literal> tables, require opening the
+ <filename>.MYD</filename> or <filename>.MYI</filename> file as
+ well.
+ </para>
+
+ <para>
+ The file-opening optimization types are denoted thus:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>SKIP_OPEN_TABLE</literal>: Table files do not need
+ to be opened. The information has already become available
+ within the query by scanning the database directory.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>OPEN_FRM_ONLY</literal>: Only the table's
+ <filename>.frm</filename> file need be opened.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>OPEN_TRIGGER_ONLY</literal>: Only the table's
+ <filename>.TRG</filename> file need be opened.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>OPEN_FULL_TABLE</literal>: The unoptimized
+ information lookup. The <filename>.frm</filename>,
+ <filename>.MYD</filename>, and <filename>.MYI</filename>
+ files must be opened.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ The following list indicates how the preceding optimization
+ types apply to <literal>INFORMATION_SCHEMA</literal> table
+ columns. For tables and columns not named, none of the
+ optimizations apply.
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>COLUMNS</literal>: <literal>OPEN_FRM_ONLY</literal>
+ applies to all columns
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>KEY_COLUMN_USAGE</literal>:
+ <literal>OPEN_FULL_TABLE</literal> applies to all columns
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>PARTITIONS</literal>:
+ <literal>OPEN_FULL_TABLE</literal> applies to all columns
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>REFERENTIAL_CONSTRAINTS</literal>:
+ <literal>OPEN_FULL_TABLE</literal> applies to all columns
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>STATISTICS</literal>:
+ </para>
+
+ <informaltable>
+ <tgroup cols="2">
+ <colspec colwidth="50*"/>
+ <colspec colwidth="50*"/>
+ <tbody>
+ <row>
+ <entry><emphasis role="bold">Column</emphasis></entry>
+ <entry><emphasis role="bold">Optimization type</emphasis></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_CATALOG</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_SCHEMA</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_NAME</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>NON_UNIQUE</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>INDEX_SCHEMA</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>INDEX_NAME</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>SEQ_IN_INDEX</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>COLUMN_NAME</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>COLLATION</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>CARDINALITY</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>SUB_PART</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>PACKED</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>NULLABLE</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>INDEX_TYPE</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>COMMENT</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </informaltable>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>TABLES</literal>:
+ </para>
+
+ <informaltable>
+ <tgroup cols="2">
+ <colspec colwidth="50*"/>
+ <colspec colwidth="50*"/>
+ <tbody>
+ <row>
+ <entry><emphasis role="bold">Column</emphasis></entry>
+ <entry><emphasis role="bold">Optimization type</emphasis></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_CATALOG</literal></entry>
+ <entry><literal>SKIP_OPEN_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_SCHEMA</literal></entry>
+ <entry><literal>SKIP_OPEN_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_NAME</literal></entry>
+ <entry><literal>SKIP_OPEN_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_TYPE</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>ENGINE</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>VERSION</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>ROW_FORMAT</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_ROWS</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>AVG_ROW_LENGTH</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>DATA_LENGTH</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>MAX_DATA_LENGTH</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>INDEX_LENGTH</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>DATA_FREE</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>AUTO_INCREMENT</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>CREATE_TIME</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>UPDATE_TIME</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>CHECK_TIME</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_COLLATION</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>CHECKSUM</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>CREATE_OPTIONS</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_COMMENT</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </informaltable>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>TABLE_CONSTRAINTS</literal>:
+ <literal>OPEN_FULL_TABLE</literal> applies to all columns
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>TRIGGERS</literal>:
+ <literal>OPEN_FULL_TABLE</literal> applies to all columns
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>VIEWS</literal>:
+ </para>
+
+ <informaltable>
+ <tgroup cols="2">
+ <colspec colwidth="50*"/>
+ <colspec colwidth="50*"/>
+ <tbody>
+ <row>
+ <entry><emphasis role="bold">Column</emphasis></entry>
+ <entry><emphasis role="bold">Optimization type</emphasis></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_CATALOG</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_SCHEMA</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_NAME</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>VIEW_DEFINITION</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>CHECK_OPTION</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>IS_UPDATABLE</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>DEFINER</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>SECURITY_TYPE</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>CHARACTER_SET_CLIENT</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>COLLATION_CONNECTION</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </informaltable>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ <emphasis role="bold">3) Use <literal>EXPLAIN</literal> to
+ determine whether the server can use
+ <literal>INFORMATION_SCHEMA</literal> optimizations for a
+ query</emphasis>
+ </para>
+
+ <para>
+ The <literal>Extra</literal> value in <literal>EXPLAIN</literal>
+ output indicates which, if any, of the optimizations described
+ earlier the server can use to evaluate
+ <literal>INFORMATION_SCHEMA</literal> queries. The following
+ examples demonstrate the kinds of information you can expect to
+ see in the <literal>Extra</literal> value.
+ </para>
+
+<programlisting>mysql> <userinput>EXPLAIN SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE</userinput>
+ -> <userinput>TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'\G</userinput>
+*************************** 1. row ***************************
+ id: 1
+ select_type: SIMPLE
+ table: VIEWS
+ type: ALL
+possible_keys: NULL
+ key: TABLE_SCHEMA,TABLE_NAME
+ key_len: NULL
+ ref: NULL
+ rows: NULL
+ Extra: Using where; Open_frm_only; Scanned 0 databases
+</programlisting>
+
+ <para>
+ Use of constant database and table lookup values enables the
+ server to avoid directory scans. For references to
+ <literal>VIEWS.TABLE_NAME</literal>, only the
+ <filename>.frm</filename> file need be opened.
+ </para>
+
+<programlisting>mysql> <userinput>EXPLAIN SELECT TABLE_NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES\G</userinput>
+*************************** 1. row ***************************
+ id: 1
+ select_type: SIMPLE
+ table: TABLES
+ type: ALL
+possible_keys: NULL
+ key: NULL
+ key_len: NULL
+ ref: NULL
+ rows: NULL
+ Extra: Open_full_table; Scanned all databases
+</programlisting>
+
+ <para>
+ No lookup values are provided (there is no
+ <literal>WHERE</literal> clause), so the server must scan the
+ data directory and each database directory. For each table thus
+ identified, the table name and row format are selected.
+ <literal>TABLE_NAME</literal> requires no further table files to
+ be opened (the <literal>SKIP_OPEN_TABLE</literal> optimization
+ applies). <literal>ROW_FORMAT</literal> requires all table files
+ to be opened (<literal>OPEN_FULL_TABLE</literal> applies).
+ <literal>EXPLAIN</literal> reports
+ <literal>OPEN_FULL_TABLE</literal> because it is more expensive
+ than <literal>SKIP_OPEN_TABLE</literal>.
+ </para>
+
+<programlisting>mysql> <userinput>EXPLAIN SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES</userinput>
+ -> <userinput>WHERE TABLE_SCHEMA = 'test'\G</userinput>
+*************************** 1. row ***************************
+ id: 1
+ select_type: SIMPLE
+ table: TABLES
+ type: ALL
+possible_keys: NULL
+ key: TABLE_SCHEMA
+ key_len: NULL
+ ref: NULL
+ rows: NULL
+ Extra: Using where; Open_frm_only; Scanned 1 database
+</programlisting>
+
+ <para>
+ No table name lookup value is provided, so the server must scan
+ the <literal>test</literal> database directory. For the
+ <literal>TABLE_NAME</literal> and <literal>TABLE_TYPE</literal>
+ columns, the <literal>SKIP_OPEN_TABLE</literal> and
+ <literal>OPEN_FRM_ONLY</literal> optimizations apply,
+ respectively. <literal>EXPLAIN</literal> reports
+ <literal>OPEN_FRM_ONLY</literal> because it is more expensive.
+ </para>
+
+<programlisting>mysql> <userinput>EXPLAIN SELECT B.TABLE_NAME</userinput>
+ -> <userinput>FROM INFORMATION_SCHEMA.TABLES AS A, INFORMATION_SCHEMA.COLUMNS AS B</userinput>
+ -> <userinput>WHERE A.TABLE_SCHEMA = 'test'</userinput>
+ -> <userinput>AND A.TABLE_NAME = 't1'</userinput>
+ -> <userinput>AND B.TABLE_NAME = A.TABLE_NAME\G</userinput>
+*************************** 1. row ***************************
+ id: 1
+ select_type: SIMPLE
+ table: A
+ type: ALL
+possible_keys: NULL
+ key: TABLE_SCHEMA,TABLE_NAME
+ key_len: NULL
+ ref: NULL
+ rows: NULL
+ Extra: Using where; Skip_open_table; Scanned 0 databases
+*************************** 2. row ***************************
+ id: 1
+ select_type: SIMPLE
+ table: B
+ type: ALL
+possible_keys: NULL
+ key: NULL
+ key_len: NULL
+ ref: NULL
+ rows: NULL
+ Extra: Using where; Open_frm_only; Scanned all databases;
+ Using join buffer
+</programlisting>
+
+ <para>
+ For the first <literal>EXPLAIN</literal> output row: Constant
+ database and table lookup values enable the server to avoid
+ directory scans for <literal>TABLES</literal> values. References
+ to <literal>TABLES.TABLE_NAME</literal> require no further table
+ files.
+ </para>
+
+ <para>
+ For the second <literal>EXPLAIN</literal> output row: All
+ <literal>COLUMNS</literal> table values are
+ <literal>OPEN_FRM_ONLY</literal> lookups, so
+ <literal>COLUMNS.TABLE_NAME</literal> requires the
+ <filename>.frm</filename> file to be opened.
+ </para>
+
+<programlisting>mysql> <userinput>EXPLAIN SELECT * FROM INFORMATION_SCHEMA.COLLATIONS\G</userinput>
+*************************** 1. row ***************************
+ id: 1
+ select_type: SIMPLE
+ table: COLLATIONS
+ type: ALL
+possible_keys: NULL
+ key: NULL
+ key_len: NULL
+ ref: NULL
+ rows: NULL
+ Extra:
+</programlisting>
+
+ <para>
+ In this case, no optimizations apply because
+ <literal>COLLATIONS</literal> is not one of the
+ <literal>INFORMATION_SCHEMA</literal> tables for which
+ optimizations are available.
+ </para>
+
+ </section>
+
<section id="insert-speed">
<title>Speed of <literal>INSERT</literal> Statements</title>
@@ -6595,10 +7410,10 @@
You can increase performance by caching queries or answers
in your application and then executing many inserts or
updates together. If your database system supports table
- locks (as do MySQL and Oracle), this should help to ensure
- that the index cache is only flushed once after all updates.
- You can also take advantage of MySQL's query cache to
- achieve similar results; see <xref linkend="query-cache"/>.
+ locks, this should help to ensure that the index cache is
+ only flushed once after all updates. You can also take
+ advantage of MySQL's query cache to achieve similar results;
+ see <xref linkend="query-cache"/>.
</para>
</listitem>
@@ -8215,18 +9030,18 @@
<listitem>
<para>
- To find the <literal>MIN()</literal> or
- <literal>MAX()</literal> value for a specific indexed column
- <replaceable>key_col</replaceable>. This is optimized by a
- preprocessor that checks whether you are using
- <literal>WHERE <replaceable>key_part_N</replaceable> =
+ To find the <function role="sql">MIN()</function> or
+ <function role="sql">MAX()</function> value for a specific
+ indexed column <replaceable>key_col</replaceable>. This is
+ optimized by a preprocessor that checks whether you are
+ using <literal>WHERE <replaceable>key_part_N</replaceable> =
<replaceable>constant</replaceable></literal> on all key
parts that occur before <replaceable>key_col</replaceable>
in the index. In this case, MySQL does a single key lookup
- for each <literal>MIN()</literal> or
- <literal>MAX()</literal> expression and replaces it with a
- constant. If all expressions are replaced with constants,
- the query returns at once. For example:
+ for each <function role="sql">MIN()</function> or
+ <function role="sql">MAX()</function> expression and
+ replaces it with a constant. If all expressions are replaced
+ with constants, the query returns at once. For example:
</para>
<programlisting>
@@ -8347,14 +9162,19 @@
<para>
A B-tree index can be used for column comparisons in expressions
- that use the <literal>=</literal>, <literal>></literal>,
- <literal>>=</literal>, <literal><</literal>,
- <literal><=</literal>, or <literal>BETWEEN</literal>
- operators. The index also can be used for
- <literal>LIKE</literal> comparisons if the argument to
- <literal>LIKE</literal> is a constant string that does not start
- with a wildcard character. For example, the following
- <literal>SELECT</literal> statements use indexes:
+ that use the
+ <function role="sqlop" condition="operator_equal">=</function>,
+ <function role="sqlop" condition="operator_greater-than">></function>,
+ <function role="sqlop" condition="operator_greater-than-or-equal">>=</function>,
+ <function role="sqlop" condition="operator_less-than"><</function>,
+ <function role="sqlop" condition="operator_less-than-or-equal"><=</function>,
+ or <function role="sqlop">BETWEEN</function> operators. The
+ index also can be used for
+ <function role="sqlop">LIKE</function> comparisons if the
+ argument to <function role="sqlop">LIKE</function> is a constant
+ string that does not start with a wildcard character. For
+ example, the following <literal>SELECT</literal> statements use
+ indexes:
</para>
<programlisting>
@@ -8381,9 +9201,10 @@
</programlisting>
<para>
- In the first statement, the <literal>LIKE</literal> value begins
- with a wildcard character. In the second statement, the
- <literal>LIKE</literal> value is not a constant.
+ In the first statement, the
+ <function role="sqlop">LIKE</function> value begins with a
+ wildcard character. In the second statement, the
+ <function role="sqlop">LIKE</function> value is not a constant.
</para>
<para>
@@ -8412,10 +9233,12 @@
</para>
<para>
- Any index that does not span all <literal>AND</literal> levels
- in the <literal>WHERE</literal> clause is not used to optimize
- the query. In other words, to be able to use an index, a prefix
- of the index must be used in every <literal>AND</literal> group.
+ Any index that does not span all
+ <function role="sqlop">AND</function> levels in the
+ <literal>WHERE</literal> clause is not used to optimize the
+ query. In other words, to be able to use an index, a prefix of
+ the index must be used in every
+ <function role="sqlop">AND</function> group.
</para>
<para>
@@ -10480,8 +11303,8 @@
<listitem>
<para>
- Queries executed within the body of a stored procedure,
- stored function, trigger, or event
+ Queries executed within the body of a stored function,
+ trigger, or event
</para>
</listitem>
@@ -10519,8 +11342,8 @@
</para>
<para>
- In MySQL ¤t-series;, queries generated by views are
- cached.
+ In MySQL ¤t-series;, the results of a
+ <literal>SELECT</literal> query on a view is cached.
</para>
<para>
@@ -10603,22 +11426,22 @@
<colspec colwidth="33*"/>
<tbody>
<row>
- <entry><literal>BENCHMARK()</literal></entry>
+ <entry><function role="sql">BENCHMARK()</function></entry>
<entry><literal>CONNECTION_ID()</literal></entry>
<entry><literal>CONVERT_TZ()</literal></entry>
</row>
<row>
- <entry><literal>CURDATE()</literal></entry>
+ <entry><function role="sql">CURDATE()</function></entry>
<entry><literal>CURRENT_DATE()</literal></entry>
<entry><literal>CURRENT_TIME()</literal></entry>
</row>
<row>
<entry><literal>CURRENT_TIMESTAMP()</literal></entry>
- <entry><literal>CURTIME()</literal></entry>
- <entry><literal>DATABASE()</literal></entry>
+ <entry><function role="sql">CURTIME()</function></entry>
+ <entry><function role="sql">DATABASE()</function></entry>
</row>
<row>
- <entry><literal>ENCRYPT()</literal> with one parameter</entry>
+ <entry><function role="sql">ENCRYPT()</function> with one parameter</entry>
<entry><literal>FOUND_ROWS()</literal></entry>
<entry><literal>GET_LOCK()</literal></entry>
</row>
@@ -10628,18 +11451,18 @@
<entry><literal>MASTER_POS_WAIT()</literal></entry>
</row>
<row>
- <entry><literal>NOW()</literal></entry>
- <entry><literal>RAND()</literal></entry>
+ <entry><function role="sql">NOW()</function></entry>
+ <entry><function role="sql">RAND()</function></entry>
<entry><literal>RELEASE_LOCK()</literal></entry>
</row>
<row>
- <entry><literal>SLEEP()</literal></entry>
- <entry><literal>SYSDATE()</literal></entry>
+ <entry><function role="sql">SLEEP()</function></entry>
+ <entry><function role="sql">SYSDATE()</function></entry>
<entry><literal>UNIX_TIMESTAMP()</literal> with no parameters</entry>
</row>
<row>
<entry/>
- <entry><literal>USER()</literal></entry>
+ <entry><function role="sql">USER()</function></entry>
<entry/>
</row>
</tbody>
@@ -10661,7 +11484,8 @@
<listitem>
<para>
- It refers to user variables.
+ It refers to user variables or local stored routine
+ variables.
</para>
</listitem>
Modified: trunk/it/refman-5.1/partitioning-guide.xml
===================================================================
--- trunk/it/refman-5.1/partitioning-guide.xml 2007-12-12 19:26:14 UTC (rev 9269)
+++ trunk/it/refman-5.1/partitioning-guide.xml 2007-12-12 19:46:41 UTC (rev 9270)
Changed blocks: 9, Lines Added: 44, Lines Deleted: 37; 7946 bytes
@@ -884,11 +884,13 @@
<para>
MySQL partitioning is optimized for use with the
- <literal>TO_DAYS()</literal> and <literal>YEAR()</literal>
- functions. However, you can use other date and time functions that
- return an integer or <literal>NULL</literal>, such as
- <literal>WEEKDAY()</literal>, <literal>DAYOFYEAR()</literal>, or
- <literal>MONTH()</literal>. See
+ <literal>TO_DAYS()</literal> and
+ <function role="sql">YEAR()</function> functions. However, you can
+ use other date and time functions that return an integer or
+ <literal>NULL</literal>, such as
+ <function role="sql">WEEKDAY()</function>,
+ <function role="sql">DAYOFYEAR()</function>, or
+ <function role="sql">MONTH()</function>. See
<xref linkend="date-and-time-functions"/>, for more information
about such functions.
</para>
@@ -1425,10 +1427,11 @@
<para>
Since we cannot use character values in value-lists, we need to
convert these into integers or <literal>NULL</literal>s. For
- this purpose, we can use the <literal>ASCII()</literal> function
- on the column value. In addition — due to the use of
- different applications at different times and locations —
- these codes may be either uppercase or lowercase, and the
+ this purpose, we can use the
+ <function role="sql">ASCII()</function> function on the column
+ value. In addition — due to the use of different
+ applications at different times and locations — these
+ codes may be either uppercase or lowercase, and the
<quote>empty</quote> value representing <quote>currently
unassigned</quote> may actually be a <literal>NULL</literal>, an
empty string, or a space character. A partitioned table that
@@ -1639,19 +1642,19 @@
<para>
By way of contrast, suppose that you have a column named
<literal>int_col</literal> whose type is <literal>INT</literal>.
- Now consider the expression <literal>POW(5-int_col,3) +
- 6</literal>. This would be a poor choice for a hashing function
- because a change in the value of <literal>int_col</literal> is
- not guaranteed to produce a proportional change in the value of
- the expression. Changing the value of <literal>int_col</literal>
- by a given amount can produce by widely different changes in the
- value of the expression. For example, changing
- <literal>int_col</literal> from <literal>5</literal> to
- <literal>6</literal> produces a change of <literal>-1</literal>
- in the value of the expression, but changing the value of
- <literal>int_col</literal> from <literal>6</literal> to
- <literal>7</literal> produces a change of <literal>-7</literal>
- in the expression value.
+ Now consider the expression
+ <function role="sql">POW(5-int_col,3) + 6</function>. This would
+ be a poor choice for a hashing function because a change in the
+ value of <literal>int_col</literal> is not guaranteed to produce
+ a proportional change in the value of the expression. Changing
+ the value of <literal>int_col</literal> by a given amount can
+ produce by widely different changes in the value of the
+ expression. For example, changing <literal>int_col</literal>
+ from <literal>5</literal> to <literal>6</literal> produces a
+ change of <literal>-1</literal> in the value of the expression,
+ but changing the value of <literal>int_col</literal> from
+ <literal>6</literal> to <literal>7</literal> produces a change
+ of <literal>-7</literal> in the expression value.
</para>
<para>
@@ -1809,7 +1812,7 @@
3.7004397181411.
<literal>CEILING(3.7004397181411)</literal> is 4, and
<replaceable>V</replaceable> =
- <literal>POWER(2,4)</literal>, which is 16.)
+ <function role="sql">POWER(2,4)</function>, which is 16.)
</para>
</listitem>
@@ -1929,10 +1932,11 @@
Partitioning by key is similar to partitioning by hash, except
that where hash partitioning employs a user-defined expression,
the hashing function for key partitioning is supplied by the
- MySQL server. MySQL Cluster uses <literal>MD5()</literal> for
- this purpose; for tables using other storage engines, the server
- employs its own internal hashing function which is based on the
- same algorithm as <literal>PASSWORD()</literal>.
+ MySQL server. MySQL Cluster uses
+ <function role="sql">MD5()</function> for this purpose; for
+ tables using other storage engines, the server employs its own
+ internal hashing function which is based on the same algorithm
+ as <function role="sql">PASSWORD()</function>.
</para>
<para>
@@ -3653,8 +3657,8 @@
</programlisting>
<para>
- <literal>COALESCE</literal> works equally well with tables that
- are partitioned by <literal>HASH</literal>,
+ <function role="sql">COALESCE</function> works equally well with
+ tables that are partitioned by <literal>HASH</literal>,
<literal>KEY</literal>, <literal>LINEAR HASH</literal>, or
<literal>LINEAR KEY</literal>. Here is an example similar to the
previous one, differing only in that the table is partitioned by
@@ -4315,9 +4319,10 @@
expression consists of an equality or a range which can be reduced
to a set of equalities. It can also be employed when the
partitioning expression represents an increasing or decreasing
- relationship or uses a function such as <literal>YEAR()</literal>
- or <literal>TO_DAYS()</literal> that produces an integer value
- when applied to a <literal>DATE</literal> or
+ relationship or uses a function such as
+ <function role="sql">YEAR()</function> or
+ <literal>TO_DAYS()</literal> that produces an integer value when
+ applied to a <literal>DATE</literal> or
<literal>DATETIME</literal> column value. For example, suppose
that table <literal>t2</literal>, defined as shown here, is
partitioned on a <literal>DATE</literal> column:
@@ -5160,9 +5165,10 @@
The <literal>LINEAR</literal> keyword entails a somewhat
different algorithm. In this case, the number of the
partition in which a row is stored is calculated as the
- result of one or more logical <literal>AND</literal>
- operations. For discussion and examples of linear hashing,
- see <xref linkend="pg-linear-hash"/>.
+ result of one or more logical
+ <function role="sqlop">AND</function> operations. For
+ discussion and examples of linear hashing, see
+ <xref linkend="pg-linear-hash"/>.
</para>
</listitem>
@@ -5189,8 +5195,9 @@
keyword. This has the same effect as with tables that are
partitioned by <literal>HASH</literal>. That is, the
partition number is found using the
- <literal>&</literal> operator rather than the modulus
- (see <xref linkend="pg-linear-hash"/>, and
+ <function role="sqlop" condition="operator_bitwise-and">&</function>
+ operator rather than the modulus (see
+ <xref linkend="pg-linear-hash"/>, and
<xref linkend="pg-key"/>, for details). This example uses
linear partitioning by key to distribute data between 5
partitions:
Modified: trunk/it/refman-5.1/partitioning.xml
===================================================================
--- trunk/it/refman-5.1/partitioning.xml 2007-12-12 19:26:14 UTC (rev 9269)
+++ trunk/it/refman-5.1/partitioning.xml 2007-12-12 19:46:41 UTC (rev 9270)
Changed blocks: 16, Lines Added: 235, Lines Deleted: 420; 25142 bytes
@@ -375,39 +375,15 @@
partitions for tables using the <literal>InnoDB</literal>
storage engine.
</para>
+
+ <para>
+ <literal>DATA DIRECTORY</literal> and <literal>INDEX
+ DIRECTORY</literal> are not supported for individual
+ partitions or subpartitions on Windows (Bug #30459).
+ </para>
</note>
</para>
- <important>
- <para>
- On Windows, you must use the <literal>/</literal> character and
- not the <literal>\</literal> character when specifying paths for
- <literal>DATA DIRECTORY</literal> and <literal>INDEX
- DIRECTORY</literal>, as shown in this example:
-
-<programlisting>
-CREATE TABLE sales1 (
- id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
- sales_date DATE NOT NULL DEFAULT '0000-00-00'
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
-PARTITION BY RANGE(id)
-(
- PARTITION p0 VALUES LESS THAN (5000)
- DATA DIRECTORY = 'd:/s1/data/'
- INDEX DIRECTORY = 'd:/s1/idx/',
-
- PARTITION p1 VALUES LESS THAN (10000)
- DATA DIRECTORY = 'e:/s1/data/'
- INDEX DIRECTORY = 'e:/s1/idx/',
-
- PARTITION p2 VALUES LESS THAN MAXVALUE
- DATA DIRECTORY = 'f:/s1/data/'
- INDEX DIRECTORY = 'f:/s1/idx/'
-);
-</programlisting>
- </para>
- </important>
-
<para>
In addition, <literal>MAX_ROWS</literal> and
<literal>MIN_ROWS</literal> can be used to determine the maximum
@@ -686,11 +662,13 @@
<para>
MySQL partitioning is optimized for use with the
- <literal>TO_DAYS()</literal> and <literal>YEAR()</literal>
- functions. However, you can use other date and time functions that
- return an integer or <literal>NULL</literal>, such as
- <literal>WEEKDAY()</literal>, <literal>DAYOFYEAR()</literal>, or
- <literal>MONTH()</literal>. See
+ <literal>TO_DAYS()</literal> and
+ <function role="sql">YEAR()</function> functions. However, you can
+ use other date and time functions that return an integer or
+ <literal>NULL</literal>, such as
+ <function role="sql">WEEKDAY()</function>,
+ <function role="sql">DAYOFYEAR()</function>, or
+ <function role="sql">MONTH()</function>. See
<xref linkend="date-and-time-functions"/>, for more information
about such functions.
</para>
@@ -1388,19 +1366,19 @@
<para>
By way of contrast, suppose that you have a column named
<literal>int_col</literal> whose type is <literal>INT</literal>.
- Now consider the expression <literal>POW(5-int_col,3) +
- 6</literal>. This would be a poor choice for a hashing function
- because a change in the value of <literal>int_col</literal> is
- not guaranteed to produce a proportional change in the value of
- the expression. Changing the value of <literal>int_col</literal>
- by a given amount can produce by widely different changes in the
- value of the expression. For example, changing
- <literal>int_col</literal> from <literal>5</literal> to
- <literal>6</literal> produces a change of <literal>-1</literal>
- in the value of the expression, but changing the value of
- <literal>int_col</literal> from <literal>6</literal> to
- <literal>7</literal> produces a change of <literal>-7</literal>
- in the expression value.
+ Now consider the expression
+ <function role="sql">POW(5-int_col,3) + 6</function>. This would
+ be a poor choice for a hashing function because a change in the
+ value of <literal>int_col</literal> is not guaranteed to produce
+ a proportional change in the value of the expression. Changing
+ the value of <literal>int_col</literal> by a given amount can
+ produce by widely different changes in the value of the
+ expression. For example, changing <literal>int_col</literal>
+ from <literal>5</literal> to <literal>6</literal> produces a
+ change of <literal>-1</literal> in the value of the expression,
+ but changing the value of <literal>int_col</literal> from
+ <literal>6</literal> to <literal>7</literal> produces a change
+ of <literal>-7</literal> in the expression value.
</para>
<para>
@@ -1557,7 +1535,7 @@
3.7004397181411.
<literal>CEILING(3.7004397181411)</literal> is 4, and
<replaceable>V</replaceable> =
- <literal>POWER(2,4)</literal>, which is 16.)
+ <function role="sql">POWER(2,4)</function>, which is 16.)
</para>
</listitem>
@@ -1601,7 +1579,6 @@
For example, suppose that the table <literal>t1</literal>,
using linear hash partitioning and having 6 partitions, is
created using this statement:
- </para>
<programlisting>
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
@@ -1609,16 +1586,14 @@
PARTITIONS 6;
</programlisting>
- <para>
Now assume that you want to insert two records into
<literal>t1</literal> having the <literal>col3</literal>
column values <literal>'2003-04-14'</literal> and
<literal>'1998-10-19'</literal>. The partition number for the
first of these is determined as follows:
- </para>
<programlisting>
-<replaceable>V</replaceable> = POWER(2, CEILING( LOG(2,7) )) = 8
+<replaceable>V</replaceable> = POWER(2, CEILING( LOG(2,6) )) = 8
<replaceable>N</replaceable> = YEAR('2003-04-14') & (8 - 1)
= 2003 & 7
= 3
@@ -1626,10 +1601,8 @@
(<emphasis>3 >= 6 is FALSE: record stored in partition #3</emphasis>)
</programlisting>
- <para>
The number of the partition where the second record is stored
is calculated as shown here:
- </para>
<programlisting>
<replaceable>V</replaceable> = 8
@@ -1639,12 +1612,13 @@
(<emphasis>6 >= 6 is TRUE: additional step required</emphasis>)
-<replaceable>N</replaceable> = 6 & CEILING(5 / 2)
+<replaceable>N</replaceable> = 6 & CEILING(8 / 2)
= 6 & 3
= 2
(<emphasis>2 >= 6 is FALSE: record stored in partition #2</emphasis>)
</programlisting>
+ </para>
<para>
The advantage in partitioning by linear hash is that the
@@ -1677,10 +1651,11 @@
Partitioning by key is similar to partitioning by hash, except
that where hash partitioning employs a user-defined expression,
the hashing function for key partitioning is supplied by the
- MySQL server. MySQL Cluster uses <literal>MD5()</literal> for
- this purpose; for tables using other storage engines, the server
- employs its own internal hashing function which is based on the
- same algorithm as <literal>PASSWORD()</literal>.
+ MySQL server. MySQL Cluster uses
+ <function role="sql">MD5()</function> for this purpose; for
+ tables using other storage engines, the server employs its own
+ internal hashing function which is based on the same algorithm
+ as <function role="sql">PASSWORD()</function>.
</para>
<para>
@@ -4127,7 +4102,8 @@
represents an increasing or decreasing relationship. Pruning can
also be applied for tables partitioned on a
<literal>DATE</literal> or <literal>DATETIME</literal> column when
- the partitioning expression uses the <literal>YEAR()</literal> or
+ the partitioning expression uses the
+ <function role="sql">YEAR()</function> or
<literal>TO_DAYS()</literal> function.
<note>
@@ -4382,15 +4358,26 @@
<listitem>
<para>
+ Nested function calls. For example, while
+ <literal>MOD()</literal> and
+ <literal>TO_DAYS()</literal> are both permitted in
+ partitioning expressions, an expression such as
+ <literal>MOD(TO_DAYS(<replaceable>datetime_column</replaceable>),
+ 7)</literal> is not allowed.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
Declared variables or user variables.
</para>
</listitem>
</itemizedlist>
- For SQL functions which are not permitted in partitioning
- expressions, see
- <xref linkend="partitioning-limitations-functions-disallowed"/>.
+ For a list of SQL functions which are permitted in
+ partitioning expressions, see
+ <xref linkend="partitioning-limitations-functions"/>.
</para>
</formalpara>
@@ -4420,16 +4407,22 @@
<literal>[LINEAR] KEY</literal> partitioning, as discussed
elswhere in this chapter — see
<xref linkend="partitioning-types"/>, for more information).
+ The <literal>DIV</literal> operator is also supported. (Bug
+ #30188)
</para>
</formalpara>
<para>
Beginning with MySQL 5.1.12, the bit operators
- <literal>|</literal>, <literal>&</literal>,
- <literal>^</literal>, <literal><<</literal>,
- <literal>>></literal>, and <literal>~</literal> are not
- permitted in partitioning expressions.
+ <function role="sqlop" condition="operator_bitwise-or">|</function>,
+ <function role="sqlop" condition="operator_bitwise-and">&</function>,
+ <function role="sqlop" condition="operator_bitwise-xor">^</function>,
+ <function role="sqlop" condition="operator_left-shift"><<</function>,
+ <function role="sqlop" condition="operator_right-shift">>></function>,
+ and
+ <function role="sqlop" condition="operator_bitwise-invert">~</function>
+ are not permitted in partitioning expressions.
</para>
</listitem>
@@ -4740,6 +4733,42 @@
</formalpara>
</listitem>
+ <listitem>
+ <formalpara>
+
+ <title><literal>DATA DIRECTORY</literal> and <literal>INDEX DIRECTORY</literal>
+ options</title>
+
+ <para>
+ <literal>DATA DIRECTORY</literal> and <literal>INDEX
+ DIRECTORY</literal> are subject to the following
+ restrictions when used with partitioned tables:
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Beginning with MySQL 5.1.23, table-level <literal>DATA
+ DIRECTORY</literal> and <literal>INDEX
+ DIRECTORY</literal> are ignored. (Bug #32091)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ On Windows, the <literal>DATA DIRECTORY</literal> and
+ <literal>INDEX DIRECTORY</literal> options are not
+ supported for individual partitions or subpartitions
+ (Bug #30459).
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </para>
+
+ </formalpara>
+ </listitem>
+
</itemizedlist>
<section id="partitioning-limitations-partitioning-keys-unique-keys">
@@ -5154,8 +5183,8 @@
<para>
When performing an upgrade, tables which are partitioned by
- <literal>KEY</literal> and that use any storage engine other
- than <literal>NDBCLUSTER</literal> and must be dumped and
+ <literal>KEY</literal> and which use any storage engine other
+ than <literal>NDBCLUSTER</literal> must be dumped and
reloaded.
</para>
@@ -5182,173 +5211,167 @@
<para>
This section discusses limitations in MySQL Partitioning
relating specifically to functions used in partitioning
- expressions, including both those function which are
- specifically supported for use in partitioning expressions, and
- those which are specifically prohibited.
+ expressions.
</para>
- <section id="partitioning-limitations-functions-supported">
+ <indexterm>
+ <primary>partitioning</primary>
+ <secondary>functions supported in partitioning expressions</secondary>
+ </indexterm>
- <title>Functions Supported in Partitioning Expressions</title>
+ <para>
+ Beginning with MySQL 5.1.12, only the following MySQL functions
+ are supported in partitioning expressions:
+ </para>
- <indexterm>
- <primary>partitioning</primary>
- <secondary>functions supported in partitioning expressions</secondary>
- </indexterm>
+ <itemizedlist>
- <para>
- Beginning with MySQL 5.1.12, only the following MySQL
- functions are specifically supported in partitioning
- expressions:
- </para>
+ <listitem>
+ <para>
+ <function role="sql">ABS()</function>
+ </para>
+ </listitem>
- <itemizedlist>
+ <listitem>
+ <para>
+ <function role="sql">CEILING()</function> (see
+ <citetitle><function role="sql">CEILING()</function> and
+ <function role="sql">FLOOR()</function></citetitle>,
+ immediately following this list)
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>ABS()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">DAY()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>CEILING()</literal> (<emphasis>see the Note
- <citetitle><literal>CEILING()</literal> and
- <literal>FLOOR()</literal></citetitle> immediately
- following this list</emphasis>)
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">DAYOFMONTH()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>DAY()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">DAYOFWEEK()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>DAYOFMONTH()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">DAYOFYEAR()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>DAYOFWEEK()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">DATEDIFF()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>DAYOFYEAR()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">EXTRACT()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>DATEDIFF()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">FLOOR()</function> (see
+ <citetitle><function role="sql">CEILING()</function> and
+ <function role="sql">FLOOR()</function></citetitle>,
+ immediately following this list)
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>EXTRACT()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">HOUR()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>FLOOR()</literal> (<emphasis>see the Note
- <citetitle><literal>CEILING()</literal> and
- <literal>FLOOR()</literal></citetitle> immediately
- following this list</emphasis>)
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">MICROSECOND()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>HOUR()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">MINUTE()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>MICROSECOND()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">MOD()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>MINUTE()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">MONTH()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>MOD()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">QUARTER()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>MONTH()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">SECOND()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>QUARTER()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal>TIME_TO_SEC()</literal>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>SECOND()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal>TO_DAYS()</literal>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>TIME_TO_SEC()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">WEEKDAY()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>TO_DAYS()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">YEAR()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>WEEKDAY()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">YEARWEEK()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>YEAR()</literal>
- </para>
- </listitem>
+ </itemizedlist>
- <listitem>
- <para>
- <literal>YEARWEEK()</literal>
- </para>
- </listitem>
+ <note>
+ <formalpara>
- </itemizedlist>
+ <title><function role="sql">CEILING()</function> and
+ <function role="sql">FLOOR()</function></title>
- <note>
- <formalpara>
+ <para>
+ Each of these functions returns an integer only if it is
+ passed an integer argument. This means, for example, that
+ the following <literal>CREATE TABLE</literal> statement
+ fails with an error, as shown here:
- <title><literal>CEILING()</literal> and <literal>FLOOR()</literal></title>
-
- <para>
- Each of these functions returns an integer only if it is
- passed an integer argument. This means, for example, that
- the following <literal>CREATE TABLE</literal> statement
- fails with an error, as shown here:
-
<programlisting>
mysql> <userinput>CREATE TABLE t (c FLOAT) PARTITION BY LIST( FLOOR(c) )(</userinput>
-> <userinput>PARTITION p0 VALUES IN (1,3,5),</userinput>
@@ -5357,221 +5380,13 @@
<errortext>ERROR 1490 (HY000): The PARTITION function returns the wrong type</errortext>
</programlisting>
- See <xref linkend="mathematical-functions"/>, for more
- information about the return types of these functions.
- </para>
+ See <xref linkend="mathematical-functions"/>, for more
+ information about the return types of these functions.
+ </para>
- </formalpara>
- </note>
+ </formalpara>
+ </note>
- </section>
-
- <section id="partitioning-limitations-functions-disallowed">
-
- <title>Functions Not Permitted in Partitioning Expressions</title>
-
- <indexterm>
- <primary>partitioning</primary>
- <secondary>functions disallowed in partitioning expressions</secondary>
- </indexterm>
-
- <para>
- Beginning with MySQL 5.1.12, the following MySQL functions are
- specifically not allowed in partitioning expressions:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <literal>ASCII()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>BIT_COUNT()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>BIT_LENGTH()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>CASE()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>CAST()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>CHAR_LENGTH()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>CHARACTER_LENGTH()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>CONVERT()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>CRC32()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>FIND_IN_SET()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>GREATEST()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>IFNULL()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>INET_ATON()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>INSTR()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>ISNULL()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>LEAST()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>LENGTH()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>LOCATE()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>NULLIF()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>OCTET_LENGTH()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>ORD()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>PERIOD_ADD()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>PERIOD_DIFF()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>POSITION()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>ROUND()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>SIGN()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>STRCMP()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>TIMESTAMPDIFF()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>UNIX_TIMESTAMP()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>WEEK()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>WEEKOFYEAR()</literal>
- </para>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
</section>
</section>
Modified: trunk/pt/refman-5.1/mysql-cluster-configuration.xml
===================================================================
--- trunk/pt/refman-5.1/mysql-cluster-configuration.xml 2007-12-12 19:26:14 UTC (rev 9269)
+++ trunk/pt/refman-5.1/mysql-cluster-configuration.xml 2007-12-12 19:46:41 UTC (rev 9270)
Changed blocks: 1, Lines Added: 30, Lines Deleted: 24; 3630 bytes
@@ -1927,41 +1927,47 @@
</para>
<para>
- For each active transaction in the cluster there must be a
- record in one of the cluster nodes. The task of coordinating
- transactions is spread among the nodes. The total number of
- transaction records in the cluster is the number of
- transactions in any given node times the number of nodes in
- the cluster.
+ Each cluster data node requires a transaction record for
+ each active transaction in the cluster. The task of
+ coordinating transactions is distributed among all of the
+ data nodes. The total number of transaction records in the
+ cluster is the number of transactions in any given node
+ times the number of nodes in the cluster.
</para>
<para>
Transaction records are allocated to individual MySQL
- servers. Normally, there is at least one transaction record
- allocated per connection that using any table in the
- cluster. For this reason, one should ensure that there are
- more transaction records in the cluster than there are
- concurrent connections to all MySQL servers in the cluster.
+ servers. Each connection to a MySQL server requires at least
+ one transaction record, plus an additional transaction
+ object per table accessed by that connection. This means
+ that a reasonable minimum for this parameter is
+
+<programlisting>
+MaxNoOfConcurrentTransactions =
+ (maximum number of tables accessed in any single transaction + 1)
+ * number of cluster SQL nodes
+</programlisting>
+
+ For example, suppose that there are 4 SQL nodes using the
+ cluster. A single join involving 5 tables requires 6
+ transaction records; if there are 5 such joins in a
+ transaction, then 5 * 6 = 30 transaction records are
+ required for this transaction, per MySQL server, or 30 * 4 =
+ 120 transaction records total.
</para>
<para>
This parameter must be set to the same value for all cluster
- nodes.
+ data nodes. This is due to the fact that, when a data node
+ fails, the oldest surviving node re-creates the transaction
+ state of all transactions that were ongoing in the failed
+ node.
</para>
- <remark role="todo">
- [js] What does the following really mean? If we shouldn't
- change this, then why is there an option to do so?
- </remark>
-
<para>
- Changing this parameter is never safe and doing so can cause
- a cluster to crash. When a node crashes, one of the nodes
- (actually the oldest surviving node) will build up the
- transaction state of all transactions ongoing in the crashed
- node at the time of the crash. It is thus important that
- this node has as many transaction records as the failed
- node.
+ Changing the value of
+ <literal>MaxNoOfConcurrentTransactions</literal> requires a
+ complete shutdown and restart of the cluster.
</para>
<para>
Modified: trunk/pt/refman-5.1/optimization.xml
===================================================================
--- trunk/pt/refman-5.1/optimization.xml 2007-12-12 19:26:14 UTC (rev 9269)
+++ trunk/pt/refman-5.1/optimization.xml 2007-12-12 19:46:41 UTC (rev 9270)
Changed blocks: 39, Lines Added: 961, Lines Deleted: 137; 58404 bytes
@@ -228,12 +228,10 @@
feeling for each SQL server's bottlenecks. For example, MySQL is
very fast in retrieving and updating rows for
<literal>MyISAM</literal> tables, but has a problem in mixing
- slow readers and writers on the same table. Oracle, on the other
- hand, has a big problem when you try to access rows that you
- have recently updated (until they are flushed to disk).
- Transactional database systems in general are not very good at
- generating summary tables from log tables, because in this case
- row locking is almost useless.
+ slow readers and writers on the same table. Transactional
+ database systems in general are not very good at generating
+ summary tables from log tables, because in this case row locking
+ is almost useless.
</para>
<formalpara role="mnmas">
@@ -645,7 +643,7 @@
<para>
If your problem is with a specific MySQL expression or function,
you can perform a timing test by invoking the
- <literal>BENCHMARK()</literal> function using the
+ <function role="sql">BENCHMARK()</function> function using the
<command>mysql</command> client program. Its syntax is
<literal>BENCHMARK(<replaceable>loop_count</replaceable>,<replaceable>expression</replaceable>)</literal>.
The return value is always zero, but <command>mysql</command>
@@ -671,9 +669,9 @@
<para>
All MySQL functions should be highly optimized, but there may be
- some exceptions. <literal>BENCHMARK()</literal> is an excellent
- tool for finding out if some function is a problem for your
- queries.
+ some exceptions. <function role="sql">BENCHMARK()</function> is an
+ excellent tool for finding out if some function is a problem for
+ your queries.
</para>
<section id="explain">
@@ -1197,13 +1195,16 @@
<para>
<literal>range</literal> can be used when a key column
is compared to a constant using any of the
- <literal>=</literal>, <literal><></literal>,
- <literal>></literal>, <literal>>=</literal>,
- <literal><</literal>, <literal><=</literal>,
- <literal>IS NULL</literal>,
- <literal><=></literal>,
- <literal>BETWEEN</literal>, or <literal>IN</literal>
- operators:
+ <function role="sqlop" condition="operator_equal">=</function>,
+ <function role="sqlop" condition="operator_not-equal"><></function>,
+ <function role="sqlop" condition="operator_greater-than">></function>,
+ <function role="sqlop" condition="operator_greater-than-or-equal">>=</function>,
+ <function role="sqlop" condition="operator_less-than"><</function>,
+ <function role="sqlop" condition="operator_less-than-or-equal"><=</function>,
+ <function role="sqlop">IS NULL</function>,
+ <function role="sqlop" condition="operator_equal-to"><=></function>,
+ <function role="sqlop">BETWEEN</function>, or
+ <function role="sql">IN()</function> operators:
</para>
<programlisting>
@@ -1525,13 +1526,31 @@
<listitem>
<para>
+ <literal>Scanned <replaceable>N</replaceable>
+ databases</literal>
+ </para>
+
+ <para>
+ This indicates how many directory scans the server
+ performs when processing a query for
+ <literal>INFORMATION_SCHEMA</literal> tables, as
+ described in
+ <xref linkend="information-schema-optimization"/>. The
+ value of <replaceable>N</replaceable> can be 0, 1, or
+ <literal>all</literal>.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
<literal>Select tables optimized away</literal>
</para>
<para>
The query contained only aggregate functions
- (<literal>MIN()</literal>, <literal>MAX()</literal>)
- that were all resolved using an index, or
+ (<function role="sql">MIN()</function>,
+ <function role="sql">MAX()</function>) that were all
+ resolved using an index, or
<function role="sql">COUNT(*)</function> for
<literal>MyISAM</literal>, and no <literal>GROUP
BY</literal> clause. The optimizer determined that only
@@ -1541,6 +1560,60 @@
<listitem>
<para>
+ <literal>Skip_open_table</literal>,
+ <literal>Open_frm_only</literal>,
+ <literal>Open_trigger_only</literal>,
+ <literal>Open_full_table</literal>
+ </para>
+
+ <para>
+ These values indicate file-opening optimizations that
+ apply to queries for
+ <literal>INFORMATION_SCHEMA</literal> tables, as
+ described in
+ <xref linkend="information-schema-optimization"/>.
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>Skip_open_table</literal>: Table files do
+ not need to be opened. The information has already
+ become available within the query by scanning the
+ database directory.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>Open_frm_only</literal>: Only the table's
+ <filename>.frm</filename> file need be opened.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>Open_trigger_only</literal>: Only the
+ table's <filename>.TRG</filename> file need be
+ opened.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>Open_full_table</literal>: The unoptimized
+ information lookup. The <filename>.frm</filename>,
+ <filename>.MYD</filename>, and
+ <filename>.MYI</filename> files must be opened.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </listitem>
+
+ <listitem>
+ <para>
<literal>Using filesort</literal>
</para>
@@ -2338,7 +2411,7 @@
<literal>WHERE</literal> if you do not use <literal>GROUP
BY</literal> or aggregate functions
(<function role="sql">COUNT()</function>,
- <literal>MIN()</literal>, and so on).
+ <function role="sql">MIN()</function>, and so on).
</para>
</listitem>
@@ -2540,9 +2613,11 @@
For both <literal>BTREE</literal> and
<literal>HASH</literal> indexes, comparison of a key part
with a constant value is a range condition when using the
- <literal>=</literal>, <literal><=></literal>,
- <literal>IN</literal>, <literal>IS NULL</literal>, or
- <literal>IS NOT NULL</literal> operators.
+ <function role="sqlop" condition="operator_equal">=</function>,
+ <function role="sqlop" condition="operator_equal-to"><=></function>,
+ <function role="sql">IN()</function>,
+ <function role="sqlop">IS NULL</function>, or
+ <function role="sqlop">IS NOT NULL</function> operators.
</para>
</listitem>
@@ -2550,21 +2625,28 @@
<para>
For <literal>BTREE</literal> indexes, comparison of a key
part with a constant value is a range condition when using
- the <literal>></literal>, <literal><</literal>,
- <literal>>=</literal>, <literal><=</literal>,
- <literal>BETWEEN</literal>, <literal>!=</literal>, or
- <literal><></literal> operators, or <literal>LIKE
- '<replaceable>pattern</replaceable>'</literal> (where
- <literal>'<replaceable>pattern</replaceable>'</literal>
- does not start with a wildcard).
+ the
+ <function role="sqlop" condition="operator_greater-than">></function>,
+ <function role="sqlop" condition="operator_less-than"><</function>,
+ <function role="sqlop" condition="operator_greater-than-or-equal">>=</function>,
+ <function role="sqlop" condition="operator_less-than-or-equal"><=</function>,
+ <function role="sqlop">BETWEEN</function>,
+ <function role="sqlop" condition="operator_not-equal">!=</function>,
+ or
+ <function role="sqlop" condition="operator_not-equal"><></function>
+ operators, or <function role="sqlop">LIKE</function>
+ comparisons if the argument to
+ <function role="sqlop">LIKE</function> is a constant
+ string that does not start with a wildcard character.
</para>
</listitem>
<listitem>
<para>
For all types of indexes, multiple range conditions
- combined with <literal>OR</literal> or
- <literal>AND</literal> form a range condition.
+ combined with <function role="sqlop">OR</function> or
+ <function role="sqlop">AND</function> form a range
+ condition.
</para>
</listitem>
@@ -2751,9 +2833,10 @@
<para>
The range condition extraction algorithm can handle nested
- <literal>AND</literal>/<literal>OR</literal> constructs of
- arbitrary depth, and its output does not depend on the order
- in which conditions appear in <literal>WHERE</literal> clause.
+ <function role="sqlop">AND</function>/<function role="sqlop">OR</function>
+ constructs of arbitrary depth, and its output does not depend
+ on the order in which conditions appear in
+ <literal>WHERE</literal> clause.
</para>
<para>
@@ -2846,13 +2929,14 @@
Here, <replaceable>const1</replaceable>,
<replaceable>const2</replaceable>, … are constants,
<replaceable>cmp</replaceable> is one of the
- <literal>=</literal>, <literal><=></literal>, or
- <literal>IS NULL</literal> comparison operators, and the
- conditions cover all index parts. (That is, there are
- <replaceable>N</replaceable> conditions, one for each part
- of an <replaceable>N</replaceable>-part index.) For
- example, the following is a range condition for a
- three-part <literal>HASH</literal> index:
+ <function role="sqlop" condition="operator_equal">=</function>,
+ <function role="sqlop" condition="operator_equal-to"><=></function>,
+ or <function role="sqlop">IS NULL</function> comparison
+ operators, and the conditions cover all index parts. (That
+ is, there are <replaceable>N</replaceable> conditions, one
+ for each part of an <replaceable>N</replaceable>-part
+ index.) For example, the following is a range condition
+ for a three-part <literal>HASH</literal> index:
</para>
<programlisting>
@@ -2869,21 +2953,29 @@
<para>
For a <literal>BTREE</literal> index, an interval might be
usable for conditions combined with
- <literal>AND</literal>, where each condition compares a
- key part with a constant value using <literal>=</literal>,
- <literal><=></literal>, <literal>IS NULL</literal>,
- <literal>></literal>, <literal><</literal>,
- <literal>>=</literal>, <literal><=</literal>,
- <literal>!=</literal>, <literal><></literal>,
- <literal>BETWEEN</literal>, or <literal>LIKE
- '<replaceable>pattern</replaceable>'</literal> (where
+ <function role="sqlop">AND</function>, where each
+ condition compares a key part with a constant value using
+ <function role="sqlop" condition="operator_equal">=</function>,
+ <function role="sqlop" condition="operator_equal-to"><=></function>,
+ <function role="sqlop">IS NULL</function>,
+ <function role="sqlop" condition="operator_greater-than">></function>,
+ <function role="sqlop" condition="operator_less-than"><</function>,
+ <function role="sqlop" condition="operator_greater-than-or-equal">>=</function>,
+ <function role="sqlop" condition="operator_less-than-or-equal"><=</function>,
+ <function role="sqlop" condition="operator_not-equal">!=</function>,
+ <function role="sqlop" condition="operator_not-equal"><></function>,
+ <function role="sqlop">BETWEEN</function>, or
+ <function role="sqlop" condition="operator_like">LIKE
+ '<replaceable>pattern</replaceable>'</function> (where
<literal>'<replaceable>pattern</replaceable>'</literal>
does not start with a wildcard). An interval can be used
as long as it is possible to determine a single key tuple
containing all rows that match the condition (or two
- intervals if <literal><></literal> or
- <literal>!=</literal> is used). For example, for this
- condition:
+ intervals if
+ <function role="sqlop" condition="operator_not-equal"><></function>
+ or
+ <function role="sqlop" condition="operator_not-equal">!=</function>
+ is used). For example, for this condition:
</para>
<programlisting>
@@ -2910,11 +3002,12 @@
<listitem>
<para>
If conditions that cover sets of rows contained within
- intervals are combined with <literal>OR</literal>, they
- form a condition that covers a set of rows contained
- within the union of their intervals. If the conditions are
- combined with <literal>AND</literal>, they form a
+ intervals are combined with
+ <function role="sqlop">OR</function>, they form a
condition that covers a set of rows contained within the
+ union of their intervals. If the conditions are combined
+ with <function role="sqlop">AND</function>, they form a
+ condition that covers a set of rows contained within the
intersection of their intervals. For example, for this
condition on a two-part index:
</para>
@@ -3133,7 +3226,8 @@
<listitem>
<para>
If your query has a complex <literal>WHERE</literal> clause
- with deep <literal>AND</literal>/<literal>OR</literal>
+ with deep
+ <function role="sqlop">AND</function>/<function role="sqlop">OR</function>
nesting and MySQL doesn't choose the optimal plan, try
distributing terms using the following identity laws:
</para>
@@ -3167,8 +3261,8 @@
This access algorithm can be employed when a
<literal>WHERE</literal> clause was converted to several range
conditions on different keys combined with
- <literal>AND</literal>, and each condition is one of the
- following:
+ <function role="sqlop">AND</function>, and each condition is
+ one of the following:
</para>
<itemizedlist>
@@ -3248,8 +3342,8 @@
algorithm can be employed when the table's
<literal>WHERE</literal> clause was converted to several range
conditions on different keys combined with
- <literal>OR</literal>, and each condition is one of the
- following:
+ <function role="sqlop">OR</function>, and each condition is
+ one of the following:
</para>
<itemizedlist>
@@ -3302,8 +3396,9 @@
<para>
This access algorithm is employed when the
<literal>WHERE</literal> clause was converted to several range
- conditions combined by <literal>OR</literal>, but for which
- the Index Merge method union algorithm is not applicable.
+ conditions combined by <function role="sqlop">OR</function>,
+ but for which the Index Merge method union algorithm is not
+ applicable.
</para>
<para>
@@ -3329,7 +3424,7 @@
<section id="is-null-optimization">
- <title><literal>IS NULL</literal> Optimization</title>
+ <title><function role="sqlop">IS NULL</function> Optimization</title>
<indexterm>
<primary>IS NULL</primary>
@@ -3341,11 +3436,12 @@
<para>
MySQL can perform the same optimization on
- <replaceable>col_name</replaceable> <literal>IS NULL</literal>
- that it can use for <replaceable>col_name</replaceable>
- <literal>=</literal> <replaceable>constant_value</replaceable>.
- For example, MySQL can use indexes and ranges to search for
- <literal>NULL</literal> with <literal>IS NULL</literal>.
+ <replaceable>col_name</replaceable> <function role="sqlop">IS
+ NULL</function> that it can use for
+ <replaceable>col_name</replaceable> <literal>=</literal>
+ <replaceable>constant_value</replaceable>. For example, MySQL
+ can use indexes and ranges to search for <literal>NULL</literal>
+ with <function role="sqlop">IS NULL</function>.
</para>
<para>
@@ -3363,10 +3459,10 @@
<para>
If a <literal>WHERE</literal> clause includes a
- <replaceable>col_name</replaceable> <literal>IS NULL</literal>
- condition for a column that is declared as <literal>NOT
- NULL</literal>, that expression is optimized away. This
- optimization does not occur in cases when the column might
+ <replaceable>col_name</replaceable> <function role="sqlop">IS
+ NULL</function> condition for a column that is declared as
+ <literal>NOT NULL</literal>, that expression is optimized away.
+ This optimization does not occur in cases when the column might
produce <literal>NULL</literal> anyway; for example, if it comes
from a table on the right side of a <literal>LEFT
JOIN</literal>.
@@ -3383,8 +3479,8 @@
</para>
<para>
- This optimization can handle one <literal>IS NULL</literal> for
- any key part.
+ This optimization can handle one <function role="sqlop">IS
+ NULL</function> for any key part.
</para>
<para>
@@ -3416,11 +3512,11 @@
</para>
<para>
- Note that the optimization can handle only one <literal>IS
- NULL</literal> level. In the following query, MySQL uses key
- lookups only on the expression <literal>(t1.a=t2.a AND t2.a IS
- NULL)</literal> and is not able to use the key part on
- <literal>b</literal>:
+ Note that the optimization can handle only one
+ <function role="sqlop">IS NULL</function> level. In the
+ following query, MySQL uses key lookups only on the expression
+ <literal>(t1.a=t2.a AND t2.a IS NULL)</literal> and is not able
+ to use the key part on <literal>b</literal>:
</para>
<programlisting>
@@ -4938,8 +5034,9 @@
<listitem>
<para>
The only aggregate functions used (if any) are
- <literal>MIN()</literal> and <literal>MAX()</literal>, and
- all of them refer to the same column.
+ <function role="sql">MIN()</function> and
+ <function role="sql">MAX()</function>, and all of them
+ refer to the same column.
</para>
</listitem>
@@ -4949,8 +5046,8 @@
<literal>GROUP BY</literal> referenced in the query must
be constants (that is, they must be referenced in
equalities with constants), except for the argument of
- <literal>MIN()</literal> or <literal>MAX()</literal>
- functions.
+ <function role="sql">MIN()</function> or
+ <function role="sql">MAX()</function> functions.
</para>
</listitem>
@@ -4988,8 +5085,8 @@
<listitem>
<para>
There are aggregate functions other than
- <literal>MIN()</literal> or <literal>MAX()</literal>, for
- example:
+ <function role="sql">MIN()</function> or
+ <function role="sql">MAX()</function>, for example:
</para>
<programlisting>
@@ -5284,8 +5381,8 @@
<para>
You do not need to distinguish <literal>NULL</literal> from
<literal>FALSE</literal> subquery results. (If the subquery
- is a part of an <literal>OR</literal> or
- <literal>AND</literal> expression in the
+ is a part of an <function role="sqlop">OR</function> or
+ <function role="sqlop">AND</function> expression in the
<literal>WHERE</literal> clause, MySQL assumes that you
don't care.)
</para>
@@ -5344,9 +5441,9 @@
</programlisting>
<para>
- The need to evaluate the extra <literal>IS NULL</literal>
- condition is why MySQL has the <literal>ref_or_null</literal>
- access method:
+ The need to evaluate the extra <function role="sqlop">IS
+ NULL</function> condition is why MySQL has the
+ <literal>ref_or_null</literal> access method:
</para>
<programlisting>
@@ -5737,8 +5834,8 @@
<para>
Then <literal>NULL IN (SELECT ...)</literal> will never be
evaluated because MySQL stops evaluating
- <literal>AND</literal> parts as soon as the expression
- result is clear.
+ <function role="sqlop">AND</function> parts as soon as the
+ expression result is clear.
</para>
</listitem>
@@ -5967,6 +6064,724 @@
</section>
+ <section id="information-schema-optimization">
+
+ <title><literal>INFORMATION_SCHEMA</literal> Optimization</title>
+
+ <para>
+ In MySQL 5.1.23, changes to the implementation of
+ <literal>INFORMATION_SCHEMA</literal> were made that optimize
+ certain types of queries for
+ <literal>INFORMATION_SCHEMA</literal> tables so that they
+ execute more quickly. This section provides guidelines on
+ writing queries that take advantage of these optimizations. In
+ general, the strategies outlined here minimize the need for the
+ server to access the filesystem to obtain the information that
+ makes up the contents of <literal>INFORMATION_SCHEMA</literal>
+ tables. By writing queries that enable the server to avoid
+ directory scans or opening table files, you will obtain better
+ performance.
+ </para>
+
+ <para>
+ <emphasis role="bold">1) Try to use constant lookup values for
+ database and table names in the <literal>WHERE</literal>
+ clause</emphasis>
+ </para>
+
+ <para>
+ You can take advantage of this principle as follows:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ To look up databases or tables, use expressions that
+ evaluate to a constant, such as literal values, functions
+ that return a constant, or scalar subqueries.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Avoid queries that use a non-constant database name lookup
+ value (or no lookup value) because they require a scan of
+ the data directory to find matching database directory
+ names.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Within a database, avoid queries that use a non-constant
+ table name lookup value (or no lookup value) because they
+ require a scan of the database directory to find matching
+ table files.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ This principle applies to the
+ <literal>INFORMATION_SCHEMA</literal> tables shown in the
+ following table, which shows the columns for which a constant
+ lookup value enables the server to avoid a directory scan. For
+ example, if you are selecting from <literal>TABLES</literal>,
+ using a constant lookup value for
+ <literal>TABLE_SCHEMA</literal> in the <literal>WHERE</literal>
+ clause enables a data directory scan to be avoided.
+ </para>
+
+ <informaltable>
+ <tgroup cols="3">
+ <colspec colwidth="34*"/>
+ <colspec colwidth="33*"/>
+ <colspec colwidth="33*"/>
+ <tbody>
+ <row>
+ <entry><emphasis role="bold">Table</emphasis></entry>
+ <entry><emphasis role="bold">Column to specify to avoid data directory
+ scan</emphasis></entry>
+ <entry><emphasis role="bold">Column to specify to avoid database directory
+ scan</emphasis></entry>
+ </row>
+ <row>
+ <entry><literal>COLUMNS</literal></entry>
+ <entry><literal>TABLE_SCHEMA</literal></entry>
+ <entry><literal>TABLE_NAME</literal></entry>
+ </row>
+ <row>
+ <entry><literal>KEY_COLUMN_USAGE</literal></entry>
+ <entry><literal>TABLE_SCHEMA</literal></entry>
+ <entry><literal>TABLE_NAME</literal></entry>
+ </row>
+ <row>
+ <entry><literal>PARTITIONS</literal></entry>
+ <entry><literal>TABLE_SCHEMA</literal></entry>
+ <entry><literal>TABLE_NAME</literal></entry>
+ </row>
+ <row>
+ <entry><literal>REFERENTIAL_CONSTRAINTS</literal></entry>
+ <entry><literal>CONSTRAINT_SCHEMA</literal></entry>
+ <entry><literal>TABLE_NAME</literal></entry>
+ </row>
+ <row>
+ <entry><literal>STATISTICS</literal></entry>
+ <entry><literal>TABLE_SCHEMA</literal></entry>
+ <entry><literal>TABLE_NAME</literal></entry>
+ </row>
+ <row>
+ <entry><literal>TABLES</literal></entry>
+ <entry><literal>TABLE_SCHEMA</literal></entry>
+ <entry><literal>TABLE_NAME</literal></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_CONSTRAINTS</literal></entry>
+ <entry><literal>TABLE_SCHEMA</literal></entry>
+ <entry><literal>TABLE_NAME</literal></entry>
+ </row>
+ <row>
+ <entry><literal>TRIGGERS</literal></entry>
+ <entry><literal>EVENT_OBJECT_SCHEMA</literal></entry>
+ <entry><literal>EVENT_OBJECT_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>VIEWS</literal></entry>
+ <entry><literal>TABLE_SCHEMA</literal></entry>
+ <entry><literal>TABLE_NAME</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </informaltable>
+
+ <para>
+ The benefit of a query that is limited to a specific constant
+ database name is that checks need be made only for the named
+ database directories. Example:
+ </para>
+
+<programlisting>SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
+WHERE TABLE_SCHEMA = 'test';
+</programlisting>
+
+ <para>
+ Use of the literal database name <literal>test</literal> enables
+ the server to check only the <literal>test</literal> database
+ directory, regardless of how many databases there might be. By
+ contrast, the following query is less efficient because it
+ requires a scan of the data directory to determine which
+ database names match the pattern <literal>'test%'</literal>:
+ </para>
+
+<programlisting>SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
+WHERE TABLE_SCHEMA LIKE 'test%';
+</programlisting>
+
+ <para>
+ For a query that is limited to a specific constant table name,
+ checks need be made only for the named tables within the
+ corresponding database directory. Example:
+ </para>
+
+<programlisting>SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
+WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
+</programlisting>
+
+ <para>
+ Use of the literal table name <literal>t1</literal> enables the
+ server to check only the files for the <literal>t1</literal>
+ table, regardless of how many tables there might be in the
+ <literal>test</literal> database. By contrast, the following
+ query requires a scan of the <literal>test</literal> database
+ directory to determine which table names match the pattern
+ <literal>'t%'</literal>:
+ </para>
+
+<programlisting>SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
+WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME LIKE 't%';
+</programlisting>
+
+ <para>
+ The following query requires a scan of the database directory to
+ determine matching database names for the pattern
+ <literal>'test%'</literal>, and for each matching database, it
+ requires a scan of the database directory to determine matching
+ table names for the pattern <literal>'t%'</literal>:
+ </para>
+
+<programlisting>SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
+WHERE TABLE_SCHEMA = 'test%' AND TABLE_NAME LIKE 't%';
+</programlisting>
+
+ <para>
+ <emphasis role="bold">2) Write queries that minimize the number
+ of table files that must be opened</emphasis>
+ </para>
+
+ <para>
+ For queries that refer to certain
+ <literal>INFORMATION_SCHEMA</literal> table columns, several
+ optimizations are available that minimize the number of table
+ files that must be opened. Example:
+ </para>
+
+<programlisting>SELECT TABLE_NAME, ENGINE FROM INFORMATION_SCHEMA.TABLES
+WHERE TABLE_SCHEMA = 'test';
+</programlisting>
+
+ <para>
+ In this case, after the server has scanned the database
+ directory to determine the names of the tables in the database,
+ those names become available with no further filesystem lookups.
+ Thus, <literal>TABLE_NAME</literal> requires no files to be
+ opened. The <literal>ENGINE</literal> (storage engine) value can
+ be determined by opening the table's <filename>.frm</filename>
+ file, without touching other table files such as the
+ <filename>.MYD</filename> or <filename>.MYI</filename> file.
+ </para>
+
+ <para>
+ Some values, such as <literal>INDEX_LENGTH</literal> for
+ <literal>MyISAM</literal> tables, require opening the
+ <filename>.MYD</filename> or <filename>.MYI</filename> file as
+ well.
+ </para>
+
+ <para>
+ The file-opening optimization types are denoted thus:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>SKIP_OPEN_TABLE</literal>: Table files do not need
+ to be opened. The information has already become available
+ within the query by scanning the database directory.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>OPEN_FRM_ONLY</literal>: Only the table's
+ <filename>.frm</filename> file need be opened.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>OPEN_TRIGGER_ONLY</literal>: Only the table's
+ <filename>.TRG</filename> file need be opened.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>OPEN_FULL_TABLE</literal>: The unoptimized
+ information lookup. The <filename>.frm</filename>,
+ <filename>.MYD</filename>, and <filename>.MYI</filename>
+ files must be opened.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ The following list indicates how the preceding optimization
+ types apply to <literal>INFORMATION_SCHEMA</literal> table
+ columns. For tables and columns not named, none of the
+ optimizations apply.
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>COLUMNS</literal>: <literal>OPEN_FRM_ONLY</literal>
+ applies to all columns
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>KEY_COLUMN_USAGE</literal>:
+ <literal>OPEN_FULL_TABLE</literal> applies to all columns
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>PARTITIONS</literal>:
+ <literal>OPEN_FULL_TABLE</literal> applies to all columns
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>REFERENTIAL_CONSTRAINTS</literal>:
+ <literal>OPEN_FULL_TABLE</literal> applies to all columns
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>STATISTICS</literal>:
+ </para>
+
+ <informaltable>
+ <tgroup cols="2">
+ <colspec colwidth="50*"/>
+ <colspec colwidth="50*"/>
+ <tbody>
+ <row>
+ <entry><emphasis role="bold">Column</emphasis></entry>
+ <entry><emphasis role="bold">Optimization type</emphasis></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_CATALOG</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_SCHEMA</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_NAME</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>NON_UNIQUE</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>INDEX_SCHEMA</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>INDEX_NAME</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>SEQ_IN_INDEX</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>COLUMN_NAME</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>COLLATION</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>CARDINALITY</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>SUB_PART</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>PACKED</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>NULLABLE</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>INDEX_TYPE</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>COMMENT</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </informaltable>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>TABLES</literal>:
+ </para>
+
+ <informaltable>
+ <tgroup cols="2">
+ <colspec colwidth="50*"/>
+ <colspec colwidth="50*"/>
+ <tbody>
+ <row>
+ <entry><emphasis role="bold">Column</emphasis></entry>
+ <entry><emphasis role="bold">Optimization type</emphasis></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_CATALOG</literal></entry>
+ <entry><literal>SKIP_OPEN_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_SCHEMA</literal></entry>
+ <entry><literal>SKIP_OPEN_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_NAME</literal></entry>
+ <entry><literal>SKIP_OPEN_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_TYPE</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>ENGINE</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>VERSION</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>ROW_FORMAT</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_ROWS</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>AVG_ROW_LENGTH</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>DATA_LENGTH</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>MAX_DATA_LENGTH</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>INDEX_LENGTH</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>DATA_FREE</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>AUTO_INCREMENT</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>CREATE_TIME</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>UPDATE_TIME</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>CHECK_TIME</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_COLLATION</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>CHECKSUM</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>CREATE_OPTIONS</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_COMMENT</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </informaltable>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>TABLE_CONSTRAINTS</literal>:
+ <literal>OPEN_FULL_TABLE</literal> applies to all columns
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>TRIGGERS</literal>:
+ <literal>OPEN_FULL_TABLE</literal> applies to all columns
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>VIEWS</literal>:
+ </para>
+
+ <informaltable>
+ <tgroup cols="2">
+ <colspec colwidth="50*"/>
+ <colspec colwidth="50*"/>
+ <tbody>
+ <row>
+ <entry><emphasis role="bold">Column</emphasis></entry>
+ <entry><emphasis role="bold">Optimization type</emphasis></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_CATALOG</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_SCHEMA</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>TABLE_NAME</literal></entry>
+ <entry><literal>OPEN_FRM_ONLY</literal></entry>
+ </row>
+ <row>
+ <entry><literal>VIEW_DEFINITION</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>CHECK_OPTION</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>IS_UPDATABLE</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>DEFINER</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>SECURITY_TYPE</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>CHARACTER_SET_CLIENT</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>COLLATION_CONNECTION</literal></entry>
+ <entry><literal>OPEN_FULL_TABLE</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </informaltable>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ <emphasis role="bold">3) Use <literal>EXPLAIN</literal> to
+ determine whether the server can use
+ <literal>INFORMATION_SCHEMA</literal> optimizations for a
+ query</emphasis>
+ </para>
+
+ <para>
+ The <literal>Extra</literal> value in <literal>EXPLAIN</literal>
+ output indicates which, if any, of the optimizations described
+ earlier the server can use to evaluate
+ <literal>INFORMATION_SCHEMA</literal> queries. The following
+ examples demonstrate the kinds of information you can expect to
+ see in the <literal>Extra</literal> value.
+ </para>
+
+<programlisting>mysql> <userinput>EXPLAIN SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE</userinput>
+ -> <userinput>TABLE_SCHEMA = 'test' AND TABLE_NAME = 'v1'\G</userinput>
+*************************** 1. row ***************************
+ id: 1
+ select_type: SIMPLE
+ table: VIEWS
+ type: ALL
+possible_keys: NULL
+ key: TABLE_SCHEMA,TABLE_NAME
+ key_len: NULL
+ ref: NULL
+ rows: NULL
+ Extra: Using where; Open_frm_only; Scanned 0 databases
+</programlisting>
+
+ <para>
+ Use of constant database and table lookup values enables the
+ server to avoid directory scans. For references to
+ <literal>VIEWS.TABLE_NAME</literal>, only the
+ <filename>.frm</filename> file need be opened.
+ </para>
+
+<programlisting>mysql> <userinput>EXPLAIN SELECT TABLE_NAME, ROW_FORMAT FROM INFORMATION_SCHEMA.TABLES\G</userinput>
+*************************** 1. row ***************************
+ id: 1
+ select_type: SIMPLE
+ table: TABLES
+ type: ALL
+possible_keys: NULL
+ key: NULL
+ key_len: NULL
+ ref: NULL
+ rows: NULL
+ Extra: Open_full_table; Scanned all databases
+</programlisting>
+
+ <para>
+ No lookup values are provided (there is no
+ <literal>WHERE</literal> clause), so the server must scan the
+ data directory and each database directory. For each table thus
+ identified, the table name and row format are selected.
+ <literal>TABLE_NAME</literal> requires no further table files to
+ be opened (the <literal>SKIP_OPEN_TABLE</literal> optimization
+ applies). <literal>ROW_FORMAT</literal> requires all table files
+ to be opened (<literal>OPEN_FULL_TABLE</literal> applies).
+ <literal>EXPLAIN</literal> reports
+ <literal>OPEN_FULL_TABLE</literal> because it is more expensive
+ than <literal>SKIP_OPEN_TABLE</literal>.
+ </para>
+
+<programlisting>mysql> <userinput>EXPLAIN SELECT TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES</userinput>
+ -> <userinput>WHERE TABLE_SCHEMA = 'test'\G</userinput>
+*************************** 1. row ***************************
+ id: 1
+ select_type: SIMPLE
+ table: TABLES
+ type: ALL
+possible_keys: NULL
+ key: TABLE_SCHEMA
+ key_len: NULL
+ ref: NULL
+ rows: NULL
+ Extra: Using where; Open_frm_only; Scanned 1 database
+</programlisting>
+
+ <para>
+ No table name lookup value is provided, so the server must scan
+ the <literal>test</literal> database directory. For the
+ <literal>TABLE_NAME</literal> and <literal>TABLE_TYPE</literal>
+ columns, the <literal>SKIP_OPEN_TABLE</literal> and
+ <literal>OPEN_FRM_ONLY</literal> optimizations apply,
+ respectively. <literal>EXPLAIN</literal> reports
+ <literal>OPEN_FRM_ONLY</literal> because it is more expensive.
+ </para>
+
+<programlisting>mysql> <userinput>EXPLAIN SELECT B.TABLE_NAME</userinput>
+ -> <userinput>FROM INFORMATION_SCHEMA.TABLES AS A, INFORMATION_SCHEMA.COLUMNS AS B</userinput>
+ -> <userinput>WHERE A.TABLE_SCHEMA = 'test'</userinput>
+ -> <userinput>AND A.TABLE_NAME = 't1'</userinput>
+ -> <userinput>AND B.TABLE_NAME = A.TABLE_NAME\G</userinput>
+*************************** 1. row ***************************
+ id: 1
+ select_type: SIMPLE
+ table: A
+ type: ALL
+possible_keys: NULL
+ key: TABLE_SCHEMA,TABLE_NAME
+ key_len: NULL
+ ref: NULL
+ rows: NULL
+ Extra: Using where; Skip_open_table; Scanned 0 databases
+*************************** 2. row ***************************
+ id: 1
+ select_type: SIMPLE
+ table: B
+ type: ALL
+possible_keys: NULL
+ key: NULL
+ key_len: NULL
+ ref: NULL
+ rows: NULL
+ Extra: Using where; Open_frm_only; Scanned all databases;
+ Using join buffer
+</programlisting>
+
+ <para>
+ For the first <literal>EXPLAIN</literal> output row: Constant
+ database and table lookup values enable the server to avoid
+ directory scans for <literal>TABLES</literal> values. References
+ to <literal>TABLES.TABLE_NAME</literal> require no further table
+ files.
+ </para>
+
+ <para>
+ For the second <literal>EXPLAIN</literal> output row: All
+ <literal>COLUMNS</literal> table values are
+ <literal>OPEN_FRM_ONLY</literal> lookups, so
+ <literal>COLUMNS.TABLE_NAME</literal> requires the
+ <filename>.frm</filename> file to be opened.
+ </para>
+
+<programlisting>mysql> <userinput>EXPLAIN SELECT * FROM INFORMATION_SCHEMA.COLLATIONS\G</userinput>
+*************************** 1. row ***************************
+ id: 1
+ select_type: SIMPLE
+ table: COLLATIONS
+ type: ALL
+possible_keys: NULL
+ key: NULL
+ key_len: NULL
+ ref: NULL
+ rows: NULL
+ Extra:
+</programlisting>
+
+ <para>
+ In this case, no optimizations apply because
+ <literal>COLLATIONS</literal> is not one of the
+ <literal>INFORMATION_SCHEMA</literal> tables for which
+ optimizations are available.
+ </para>
+
+ </section>
+
<section id="insert-speed">
<title>Speed of <literal>INSERT</literal> Statements</title>
@@ -6595,10 +7410,10 @@
You can increase performance by caching queries or answers
in your application and then executing many inserts or
updates together. If your database system supports table
- locks (as do MySQL and Oracle), this should help to ensure
- that the index cache is only flushed once after all updates.
- You can also take advantage of MySQL's query cache to
- achieve similar results; see <xref linkend="query-cache"/>.
+ locks, this should help to ensure that the index cache is
+ only flushed once after all updates. You can also take
+ advantage of MySQL's query cache to achieve similar results;
+ see <xref linkend="query-cache"/>.
</para>
</listitem>
@@ -8215,18 +9030,18 @@
<listitem>
<para>
- To find the <literal>MIN()</literal> or
- <literal>MAX()</literal> value for a specific indexed column
- <replaceable>key_col</replaceable>. This is optimized by a
- preprocessor that checks whether you are using
- <literal>WHERE <replaceable>key_part_N</replaceable> =
+ To find the <function role="sql">MIN()</function> or
+ <function role="sql">MAX()</function> value for a specific
+ indexed column <replaceable>key_col</replaceable>. This is
+ optimized by a preprocessor that checks whether you are
+ using <literal>WHERE <replaceable>key_part_N</replaceable> =
<replaceable>constant</replaceable></literal> on all key
parts that occur before <replaceable>key_col</replaceable>
in the index. In this case, MySQL does a single key lookup
- for each <literal>MIN()</literal> or
- <literal>MAX()</literal> expression and replaces it with a
- constant. If all expressions are replaced with constants,
- the query returns at once. For example:
+ for each <function role="sql">MIN()</function> or
+ <function role="sql">MAX()</function> expression and
+ replaces it with a constant. If all expressions are replaced
+ with constants, the query returns at once. For example:
</para>
<programlisting>
@@ -8347,14 +9162,19 @@
<para>
A B-tree index can be used for column comparisons in expressions
- that use the <literal>=</literal>, <literal>></literal>,
- <literal>>=</literal>, <literal><</literal>,
- <literal><=</literal>, or <literal>BETWEEN</literal>
- operators. The index also can be used for
- <literal>LIKE</literal> comparisons if the argument to
- <literal>LIKE</literal> is a constant string that does not start
- with a wildcard character. For example, the following
- <literal>SELECT</literal> statements use indexes:
+ that use the
+ <function role="sqlop" condition="operator_equal">=</function>,
+ <function role="sqlop" condition="operator_greater-than">></function>,
+ <function role="sqlop" condition="operator_greater-than-or-equal">>=</function>,
+ <function role="sqlop" condition="operator_less-than"><</function>,
+ <function role="sqlop" condition="operator_less-than-or-equal"><=</function>,
+ or <function role="sqlop">BETWEEN</function> operators. The
+ index also can be used for
+ <function role="sqlop">LIKE</function> comparisons if the
+ argument to <function role="sqlop">LIKE</function> is a constant
+ string that does not start with a wildcard character. For
+ example, the following <literal>SELECT</literal> statements use
+ indexes:
</para>
<programlisting>
@@ -8381,9 +9201,10 @@
</programlisting>
<para>
- In the first statement, the <literal>LIKE</literal> value begins
- with a wildcard character. In the second statement, the
- <literal>LIKE</literal> value is not a constant.
+ In the first statement, the
+ <function role="sqlop">LIKE</function> value begins with a
+ wildcard character. In the second statement, the
+ <function role="sqlop">LIKE</function> value is not a constant.
</para>
<para>
@@ -8412,10 +9233,12 @@
</para>
<para>
- Any index that does not span all <literal>AND</literal> levels
- in the <literal>WHERE</literal> clause is not used to optimize
- the query. In other words, to be able to use an index, a prefix
- of the index must be used in every <literal>AND</literal> group.
+ Any index that does not span all
+ <function role="sqlop">AND</function> levels in the
+ <literal>WHERE</literal> clause is not used to optimize the
+ query. In other words, to be able to use an index, a prefix of
+ the index must be used in every
+ <function role="sqlop">AND</function> group.
</para>
<para>
@@ -10480,8 +11303,8 @@
<listitem>
<para>
- Queries executed within the body of a stored procedure,
- stored function, trigger, or event
+ Queries executed within the body of a stored function,
+ trigger, or event
</para>
</listitem>
@@ -10519,8 +11342,8 @@
</para>
<para>
- In MySQL ¤t-series;, queries generated by views are
- cached.
+ In MySQL ¤t-series;, the results of a
+ <literal>SELECT</literal> query on a view is cached.
</para>
<para>
@@ -10603,22 +11426,22 @@
<colspec colwidth="33*"/>
<tbody>
<row>
- <entry><literal>BENCHMARK()</literal></entry>
+ <entry><function role="sql">BENCHMARK()</function></entry>
<entry><literal>CONNECTION_ID()</literal></entry>
<entry><literal>CONVERT_TZ()</literal></entry>
</row>
<row>
- <entry><literal>CURDATE()</literal></entry>
+ <entry><function role="sql">CURDATE()</function></entry>
<entry><literal>CURRENT_DATE()</literal></entry>
<entry><literal>CURRENT_TIME()</literal></entry>
</row>
<row>
<entry><literal>CURRENT_TIMESTAMP()</literal></entry>
- <entry><literal>CURTIME()</literal></entry>
- <entry><literal>DATABASE()</literal></entry>
+ <entry><function role="sql">CURTIME()</function></entry>
+ <entry><function role="sql">DATABASE()</function></entry>
</row>
<row>
- <entry><literal>ENCRYPT()</literal> with one parameter</entry>
+ <entry><function role="sql">ENCRYPT()</function> with one parameter</entry>
<entry><literal>FOUND_ROWS()</literal></entry>
<entry><literal>GET_LOCK()</literal></entry>
</row>
@@ -10628,18 +11451,18 @@
<entry><literal>MASTER_POS_WAIT()</literal></entry>
</row>
<row>
- <entry><literal>NOW()</literal></entry>
- <entry><literal>RAND()</literal></entry>
+ <entry><function role="sql">NOW()</function></entry>
+ <entry><function role="sql">RAND()</function></entry>
<entry><literal>RELEASE_LOCK()</literal></entry>
</row>
<row>
- <entry><literal>SLEEP()</literal></entry>
- <entry><literal>SYSDATE()</literal></entry>
+ <entry><function role="sql">SLEEP()</function></entry>
+ <entry><function role="sql">SYSDATE()</function></entry>
<entry><literal>UNIX_TIMESTAMP()</literal> with no parameters</entry>
</row>
<row>
<entry/>
- <entry><literal>USER()</literal></entry>
+ <entry><function role="sql">USER()</function></entry>
<entry/>
</row>
</tbody>
@@ -10661,7 +11484,8 @@
<listitem>
<para>
- It refers to user variables.
+ It refers to user variables or local stored routine
+ variables.
</para>
</listitem>
Modified: trunk/pt/refman-5.1/partitioning-guide.xml
===================================================================
--- trunk/pt/refman-5.1/partitioning-guide.xml 2007-12-12 19:26:14 UTC (rev 9269)
+++ trunk/pt/refman-5.1/partitioning-guide.xml 2007-12-12 19:46:41 UTC (rev 9270)
Changed blocks: 9, Lines Added: 44, Lines Deleted: 37; 7946 bytes
@@ -884,11 +884,13 @@
<para>
MySQL partitioning is optimized for use with the
- <literal>TO_DAYS()</literal> and <literal>YEAR()</literal>
- functions. However, you can use other date and time functions that
- return an integer or <literal>NULL</literal>, such as
- <literal>WEEKDAY()</literal>, <literal>DAYOFYEAR()</literal>, or
- <literal>MONTH()</literal>. See
+ <literal>TO_DAYS()</literal> and
+ <function role="sql">YEAR()</function> functions. However, you can
+ use other date and time functions that return an integer or
+ <literal>NULL</literal>, such as
+ <function role="sql">WEEKDAY()</function>,
+ <function role="sql">DAYOFYEAR()</function>, or
+ <function role="sql">MONTH()</function>. See
<xref linkend="date-and-time-functions"/>, for more information
about such functions.
</para>
@@ -1425,10 +1427,11 @@
<para>
Since we cannot use character values in value-lists, we need to
convert these into integers or <literal>NULL</literal>s. For
- this purpose, we can use the <literal>ASCII()</literal> function
- on the column value. In addition — due to the use of
- different applications at different times and locations —
- these codes may be either uppercase or lowercase, and the
+ this purpose, we can use the
+ <function role="sql">ASCII()</function> function on the column
+ value. In addition — due to the use of different
+ applications at different times and locations — these
+ codes may be either uppercase or lowercase, and the
<quote>empty</quote> value representing <quote>currently
unassigned</quote> may actually be a <literal>NULL</literal>, an
empty string, or a space character. A partitioned table that
@@ -1639,19 +1642,19 @@
<para>
By way of contrast, suppose that you have a column named
<literal>int_col</literal> whose type is <literal>INT</literal>.
- Now consider the expression <literal>POW(5-int_col,3) +
- 6</literal>. This would be a poor choice for a hashing function
- because a change in the value of <literal>int_col</literal> is
- not guaranteed to produce a proportional change in the value of
- the expression. Changing the value of <literal>int_col</literal>
- by a given amount can produce by widely different changes in the
- value of the expression. For example, changing
- <literal>int_col</literal> from <literal>5</literal> to
- <literal>6</literal> produces a change of <literal>-1</literal>
- in the value of the expression, but changing the value of
- <literal>int_col</literal> from <literal>6</literal> to
- <literal>7</literal> produces a change of <literal>-7</literal>
- in the expression value.
+ Now consider the expression
+ <function role="sql">POW(5-int_col,3) + 6</function>. This would
+ be a poor choice for a hashing function because a change in the
+ value of <literal>int_col</literal> is not guaranteed to produce
+ a proportional change in the value of the expression. Changing
+ the value of <literal>int_col</literal> by a given amount can
+ produce by widely different changes in the value of the
+ expression. For example, changing <literal>int_col</literal>
+ from <literal>5</literal> to <literal>6</literal> produces a
+ change of <literal>-1</literal> in the value of the expression,
+ but changing the value of <literal>int_col</literal> from
+ <literal>6</literal> to <literal>7</literal> produces a change
+ of <literal>-7</literal> in the expression value.
</para>
<para>
@@ -1809,7 +1812,7 @@
3.7004397181411.
<literal>CEILING(3.7004397181411)</literal> is 4, and
<replaceable>V</replaceable> =
- <literal>POWER(2,4)</literal>, which is 16.)
+ <function role="sql">POWER(2,4)</function>, which is 16.)
</para>
</listitem>
@@ -1929,10 +1932,11 @@
Partitioning by key is similar to partitioning by hash, except
that where hash partitioning employs a user-defined expression,
the hashing function for key partitioning is supplied by the
- MySQL server. MySQL Cluster uses <literal>MD5()</literal> for
- this purpose; for tables using other storage engines, the server
- employs its own internal hashing function which is based on the
- same algorithm as <literal>PASSWORD()</literal>.
+ MySQL server. MySQL Cluster uses
+ <function role="sql">MD5()</function> for this purpose; for
+ tables using other storage engines, the server employs its own
+ internal hashing function which is based on the same algorithm
+ as <function role="sql">PASSWORD()</function>.
</para>
<para>
@@ -3653,8 +3657,8 @@
</programlisting>
<para>
- <literal>COALESCE</literal> works equally well with tables that
- are partitioned by <literal>HASH</literal>,
+ <function role="sql">COALESCE</function> works equally well with
+ tables that are partitioned by <literal>HASH</literal>,
<literal>KEY</literal>, <literal>LINEAR HASH</literal>, or
<literal>LINEAR KEY</literal>. Here is an example similar to the
previous one, differing only in that the table is partitioned by
@@ -4315,9 +4319,10 @@
expression consists of an equality or a range which can be reduced
to a set of equalities. It can also be employed when the
partitioning expression represents an increasing or decreasing
- relationship or uses a function such as <literal>YEAR()</literal>
- or <literal>TO_DAYS()</literal> that produces an integer value
- when applied to a <literal>DATE</literal> or
+ relationship or uses a function such as
+ <function role="sql">YEAR()</function> or
+ <literal>TO_DAYS()</literal> that produces an integer value when
+ applied to a <literal>DATE</literal> or
<literal>DATETIME</literal> column value. For example, suppose
that table <literal>t2</literal>, defined as shown here, is
partitioned on a <literal>DATE</literal> column:
@@ -5160,9 +5165,10 @@
The <literal>LINEAR</literal> keyword entails a somewhat
different algorithm. In this case, the number of the
partition in which a row is stored is calculated as the
- result of one or more logical <literal>AND</literal>
- operations. For discussion and examples of linear hashing,
- see <xref linkend="pg-linear-hash"/>.
+ result of one or more logical
+ <function role="sqlop">AND</function> operations. For
+ discussion and examples of linear hashing, see
+ <xref linkend="pg-linear-hash"/>.
</para>
</listitem>
@@ -5189,8 +5195,9 @@
keyword. This has the same effect as with tables that are
partitioned by <literal>HASH</literal>. That is, the
partition number is found using the
- <literal>&</literal> operator rather than the modulus
- (see <xref linkend="pg-linear-hash"/>, and
+ <function role="sqlop" condition="operator_bitwise-and">&</function>
+ operator rather than the modulus (see
+ <xref linkend="pg-linear-hash"/>, and
<xref linkend="pg-key"/>, for details). This example uses
linear partitioning by key to distribute data between 5
partitions:
Modified: trunk/pt/refman-5.1/partitioning.xml
===================================================================
--- trunk/pt/refman-5.1/partitioning.xml 2007-12-12 19:26:14 UTC (rev 9269)
+++ trunk/pt/refman-5.1/partitioning.xml 2007-12-12 19:46:41 UTC (rev 9270)
Changed blocks: 16, Lines Added: 235, Lines Deleted: 420; 25142 bytes
@@ -375,39 +375,15 @@
partitions for tables using the <literal>InnoDB</literal>
storage engine.
</para>
+
+ <para>
+ <literal>DATA DIRECTORY</literal> and <literal>INDEX
+ DIRECTORY</literal> are not supported for individual
+ partitions or subpartitions on Windows (Bug #30459).
+ </para>
</note>
</para>
- <important>
- <para>
- On Windows, you must use the <literal>/</literal> character and
- not the <literal>\</literal> character when specifying paths for
- <literal>DATA DIRECTORY</literal> and <literal>INDEX
- DIRECTORY</literal>, as shown in this example:
-
-<programlisting>
-CREATE TABLE sales1 (
- id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
- sales_date DATE NOT NULL DEFAULT '0000-00-00'
-) ENGINE=MyISAM DEFAULT CHARSET=latin1
-PARTITION BY RANGE(id)
-(
- PARTITION p0 VALUES LESS THAN (5000)
- DATA DIRECTORY = 'd:/s1/data/'
- INDEX DIRECTORY = 'd:/s1/idx/',
-
- PARTITION p1 VALUES LESS THAN (10000)
- DATA DIRECTORY = 'e:/s1/data/'
- INDEX DIRECTORY = 'e:/s1/idx/',
-
- PARTITION p2 VALUES LESS THAN MAXVALUE
- DATA DIRECTORY = 'f:/s1/data/'
- INDEX DIRECTORY = 'f:/s1/idx/'
-);
-</programlisting>
- </para>
- </important>
-
<para>
In addition, <literal>MAX_ROWS</literal> and
<literal>MIN_ROWS</literal> can be used to determine the maximum
@@ -686,11 +662,13 @@
<para>
MySQL partitioning is optimized for use with the
- <literal>TO_DAYS()</literal> and <literal>YEAR()</literal>
- functions. However, you can use other date and time functions that
- return an integer or <literal>NULL</literal>, such as
- <literal>WEEKDAY()</literal>, <literal>DAYOFYEAR()</literal>, or
- <literal>MONTH()</literal>. See
+ <literal>TO_DAYS()</literal> and
+ <function role="sql">YEAR()</function> functions. However, you can
+ use other date and time functions that return an integer or
+ <literal>NULL</literal>, such as
+ <function role="sql">WEEKDAY()</function>,
+ <function role="sql">DAYOFYEAR()</function>, or
+ <function role="sql">MONTH()</function>. See
<xref linkend="date-and-time-functions"/>, for more information
about such functions.
</para>
@@ -1388,19 +1366,19 @@
<para>
By way of contrast, suppose that you have a column named
<literal>int_col</literal> whose type is <literal>INT</literal>.
- Now consider the expression <literal>POW(5-int_col,3) +
- 6</literal>. This would be a poor choice for a hashing function
- because a change in the value of <literal>int_col</literal> is
- not guaranteed to produce a proportional change in the value of
- the expression. Changing the value of <literal>int_col</literal>
- by a given amount can produce by widely different changes in the
- value of the expression. For example, changing
- <literal>int_col</literal> from <literal>5</literal> to
- <literal>6</literal> produces a change of <literal>-1</literal>
- in the value of the expression, but changing the value of
- <literal>int_col</literal> from <literal>6</literal> to
- <literal>7</literal> produces a change of <literal>-7</literal>
- in the expression value.
+ Now consider the expression
+ <function role="sql">POW(5-int_col,3) + 6</function>. This would
+ be a poor choice for a hashing function because a change in the
+ value of <literal>int_col</literal> is not guaranteed to produce
+ a proportional change in the value of the expression. Changing
+ the value of <literal>int_col</literal> by a given amount can
+ produce by widely different changes in the value of the
+ expression. For example, changing <literal>int_col</literal>
+ from <literal>5</literal> to <literal>6</literal> produces a
+ change of <literal>-1</literal> in the value of the expression,
+ but changing the value of <literal>int_col</literal> from
+ <literal>6</literal> to <literal>7</literal> produces a change
+ of <literal>-7</literal> in the expression value.
</para>
<para>
@@ -1557,7 +1535,7 @@
3.7004397181411.
<literal>CEILING(3.7004397181411)</literal> is 4, and
<replaceable>V</replaceable> =
- <literal>POWER(2,4)</literal>, which is 16.)
+ <function role="sql">POWER(2,4)</function>, which is 16.)
</para>
</listitem>
@@ -1601,7 +1579,6 @@
For example, suppose that the table <literal>t1</literal>,
using linear hash partitioning and having 6 partitions, is
created using this statement:
- </para>
<programlisting>
CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
@@ -1609,16 +1586,14 @@
PARTITIONS 6;
</programlisting>
- <para>
Now assume that you want to insert two records into
<literal>t1</literal> having the <literal>col3</literal>
column values <literal>'2003-04-14'</literal> and
<literal>'1998-10-19'</literal>. The partition number for the
first of these is determined as follows:
- </para>
<programlisting>
-<replaceable>V</replaceable> = POWER(2, CEILING( LOG(2,7) )) = 8
+<replaceable>V</replaceable> = POWER(2, CEILING( LOG(2,6) )) = 8
<replaceable>N</replaceable> = YEAR('2003-04-14') & (8 - 1)
= 2003 & 7
= 3
@@ -1626,10 +1601,8 @@
(<emphasis>3 >= 6 is FALSE: record stored in partition #3</emphasis>)
</programlisting>
- <para>
The number of the partition where the second record is stored
is calculated as shown here:
- </para>
<programlisting>
<replaceable>V</replaceable> = 8
@@ -1639,12 +1612,13 @@
(<emphasis>6 >= 6 is TRUE: additional step required</emphasis>)
-<replaceable>N</replaceable> = 6 & CEILING(5 / 2)
+<replaceable>N</replaceable> = 6 & CEILING(8 / 2)
= 6 & 3
= 2
(<emphasis>2 >= 6 is FALSE: record stored in partition #2</emphasis>)
</programlisting>
+ </para>
<para>
The advantage in partitioning by linear hash is that the
@@ -1677,10 +1651,11 @@
Partitioning by key is similar to partitioning by hash, except
that where hash partitioning employs a user-defined expression,
the hashing function for key partitioning is supplied by the
- MySQL server. MySQL Cluster uses <literal>MD5()</literal> for
- this purpose; for tables using other storage engines, the server
- employs its own internal hashing function which is based on the
- same algorithm as <literal>PASSWORD()</literal>.
+ MySQL server. MySQL Cluster uses
+ <function role="sql">MD5()</function> for this purpose; for
+ tables using other storage engines, the server employs its own
+ internal hashing function which is based on the same algorithm
+ as <function role="sql">PASSWORD()</function>.
</para>
<para>
@@ -4127,7 +4102,8 @@
represents an increasing or decreasing relationship. Pruning can
also be applied for tables partitioned on a
<literal>DATE</literal> or <literal>DATETIME</literal> column when
- the partitioning expression uses the <literal>YEAR()</literal> or
+ the partitioning expression uses the
+ <function role="sql">YEAR()</function> or
<literal>TO_DAYS()</literal> function.
<note>
@@ -4382,15 +4358,26 @@
<listitem>
<para>
+ Nested function calls. For example, while
+ <literal>MOD()</literal> and
+ <literal>TO_DAYS()</literal> are both permitted in
+ partitioning expressions, an expression such as
+ <literal>MOD(TO_DAYS(<replaceable>datetime_column</replaceable>),
+ 7)</literal> is not allowed.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
Declared variables or user variables.
</para>
</listitem>
</itemizedlist>
- For SQL functions which are not permitted in partitioning
- expressions, see
- <xref linkend="partitioning-limitations-functions-disallowed"/>.
+ For a list of SQL functions which are permitted in
+ partitioning expressions, see
+ <xref linkend="partitioning-limitations-functions"/>.
</para>
</formalpara>
@@ -4420,16 +4407,22 @@
<literal>[LINEAR] KEY</literal> partitioning, as discussed
elswhere in this chapter — see
<xref linkend="partitioning-types"/>, for more information).
+ The <literal>DIV</literal> operator is also supported. (Bug
+ #30188)
</para>
</formalpara>
<para>
Beginning with MySQL 5.1.12, the bit operators
- <literal>|</literal>, <literal>&</literal>,
- <literal>^</literal>, <literal><<</literal>,
- <literal>>></literal>, and <literal>~</literal> are not
- permitted in partitioning expressions.
+ <function role="sqlop" condition="operator_bitwise-or">|</function>,
+ <function role="sqlop" condition="operator_bitwise-and">&</function>,
+ <function role="sqlop" condition="operator_bitwise-xor">^</function>,
+ <function role="sqlop" condition="operator_left-shift"><<</function>,
+ <function role="sqlop" condition="operator_right-shift">>></function>,
+ and
+ <function role="sqlop" condition="operator_bitwise-invert">~</function>
+ are not permitted in partitioning expressions.
</para>
</listitem>
@@ -4740,6 +4733,42 @@
</formalpara>
</listitem>
+ <listitem>
+ <formalpara>
+
+ <title><literal>DATA DIRECTORY</literal> and <literal>INDEX DIRECTORY</literal>
+ options</title>
+
+ <para>
+ <literal>DATA DIRECTORY</literal> and <literal>INDEX
+ DIRECTORY</literal> are subject to the following
+ restrictions when used with partitioned tables:
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Beginning with MySQL 5.1.23, table-level <literal>DATA
+ DIRECTORY</literal> and <literal>INDEX
+ DIRECTORY</literal> are ignored. (Bug #32091)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ On Windows, the <literal>DATA DIRECTORY</literal> and
+ <literal>INDEX DIRECTORY</literal> options are not
+ supported for individual partitions or subpartitions
+ (Bug #30459).
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </para>
+
+ </formalpara>
+ </listitem>
+
</itemizedlist>
<section id="partitioning-limitations-partitioning-keys-unique-keys">
@@ -5154,8 +5183,8 @@
<para>
When performing an upgrade, tables which are partitioned by
- <literal>KEY</literal> and that use any storage engine other
- than <literal>NDBCLUSTER</literal> and must be dumped and
+ <literal>KEY</literal> and which use any storage engine other
+ than <literal>NDBCLUSTER</literal> must be dumped and
reloaded.
</para>
@@ -5182,173 +5211,167 @@
<para>
This section discusses limitations in MySQL Partitioning
relating specifically to functions used in partitioning
- expressions, including both those function which are
- specifically supported for use in partitioning expressions, and
- those which are specifically prohibited.
+ expressions.
</para>
- <section id="partitioning-limitations-functions-supported">
+ <indexterm>
+ <primary>partitioning</primary>
+ <secondary>functions supported in partitioning expressions</secondary>
+ </indexterm>
- <title>Functions Supported in Partitioning Expressions</title>
+ <para>
+ Beginning with MySQL 5.1.12, only the following MySQL functions
+ are supported in partitioning expressions:
+ </para>
- <indexterm>
- <primary>partitioning</primary>
- <secondary>functions supported in partitioning expressions</secondary>
- </indexterm>
+ <itemizedlist>
- <para>
- Beginning with MySQL 5.1.12, only the following MySQL
- functions are specifically supported in partitioning
- expressions:
- </para>
+ <listitem>
+ <para>
+ <function role="sql">ABS()</function>
+ </para>
+ </listitem>
- <itemizedlist>
+ <listitem>
+ <para>
+ <function role="sql">CEILING()</function> (see
+ <citetitle><function role="sql">CEILING()</function> and
+ <function role="sql">FLOOR()</function></citetitle>,
+ immediately following this list)
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>ABS()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">DAY()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>CEILING()</literal> (<emphasis>see the Note
- <citetitle><literal>CEILING()</literal> and
- <literal>FLOOR()</literal></citetitle> immediately
- following this list</emphasis>)
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">DAYOFMONTH()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>DAY()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">DAYOFWEEK()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>DAYOFMONTH()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">DAYOFYEAR()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>DAYOFWEEK()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">DATEDIFF()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>DAYOFYEAR()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">EXTRACT()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>DATEDIFF()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">FLOOR()</function> (see
+ <citetitle><function role="sql">CEILING()</function> and
+ <function role="sql">FLOOR()</function></citetitle>,
+ immediately following this list)
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>EXTRACT()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">HOUR()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>FLOOR()</literal> (<emphasis>see the Note
- <citetitle><literal>CEILING()</literal> and
- <literal>FLOOR()</literal></citetitle> immediately
- following this list</emphasis>)
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">MICROSECOND()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>HOUR()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">MINUTE()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>MICROSECOND()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">MOD()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>MINUTE()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">MONTH()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>MOD()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">QUARTER()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>MONTH()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">SECOND()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>QUARTER()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal>TIME_TO_SEC()</literal>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>SECOND()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <literal>TO_DAYS()</literal>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>TIME_TO_SEC()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">WEEKDAY()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>TO_DAYS()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">YEAR()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>WEEKDAY()</literal>
- </para>
- </listitem>
+ <listitem>
+ <para>
+ <function role="sql">YEARWEEK()</function>
+ </para>
+ </listitem>
- <listitem>
- <para>
- <literal>YEAR()</literal>
- </para>
- </listitem>
+ </itemizedlist>
- <listitem>
- <para>
- <literal>YEARWEEK()</literal>
- </para>
- </listitem>
+ <note>
+ <formalpara>
- </itemizedlist>
+ <title><function role="sql">CEILING()</function> and
+ <function role="sql">FLOOR()</function></title>
- <note>
- <formalpara>
+ <para>
+ Each of these functions returns an integer only if it is
+ passed an integer argument. This means, for example, that
+ the following <literal>CREATE TABLE</literal> statement
+ fails with an error, as shown here:
- <title><literal>CEILING()</literal> and <literal>FLOOR()</literal></title>
-
- <para>
- Each of these functions returns an integer only if it is
- passed an integer argument. This means, for example, that
- the following <literal>CREATE TABLE</literal> statement
- fails with an error, as shown here:
-
<programlisting>
mysql> <userinput>CREATE TABLE t (c FLOAT) PARTITION BY LIST( FLOOR(c) )(</userinput>
-> <userinput>PARTITION p0 VALUES IN (1,3,5),</userinput>
@@ -5357,221 +5380,13 @@
<errortext>ERROR 1490 (HY000): The PARTITION function returns the wrong type</errortext>
</programlisting>
- See <xref linkend="mathematical-functions"/>, for more
- information about the return types of these functions.
- </para>
+ See <xref linkend="mathematical-functions"/>, for more
+ information about the return types of these functions.
+ </para>
- </formalpara>
- </note>
+ </formalpara>
+ </note>
- </section>
-
- <section id="partitioning-limitations-functions-disallowed">
-
- <title>Functions Not Permitted in Partitioning Expressions</title>
-
- <indexterm>
- <primary>partitioning</primary>
- <secondary>functions disallowed in partitioning expressions</secondary>
- </indexterm>
-
- <para>
- Beginning with MySQL 5.1.12, the following MySQL functions are
- specifically not allowed in partitioning expressions:
- </para>
-
- <itemizedlist>
-
- <listitem>
- <para>
- <literal>ASCII()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>BIT_COUNT()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>BIT_LENGTH()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>CASE()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>CAST()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>CHAR_LENGTH()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>CHARACTER_LENGTH()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>CONVERT()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>CRC32()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>FIND_IN_SET()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>GREATEST()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>IFNULL()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>INET_ATON()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>INSTR()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>ISNULL()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>LEAST()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>LENGTH()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>LOCATE()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>NULLIF()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>OCTET_LENGTH()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>ORD()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>PERIOD_ADD()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>PERIOD_DIFF()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>POSITION()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>ROUND()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>SIGN()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>STRCMP()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>TIMESTAMPDIFF()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>UNIX_TIMESTAMP()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>WEEK()</literal>
- </para>
- </listitem>
-
- <listitem>
- <para>
- <literal>WEEKOFYEAR()</literal>
- </para>
- </listitem>
-
- </itemizedlist>
-
- </section>
-
</section>
</section>
Modified: trunk/refman-5.1/renamed-nodes.txt
===================================================================
--- trunk/refman-5.1/renamed-nodes.txt 2007-12-12 19:26:14 UTC (rev 9269)
+++ trunk/refman-5.1/renamed-nodes.txt 2007-12-12 19:46:41 UTC (rev 9270)
Changed blocks: 1, Lines Added: 1, Lines Deleted: 1; 805 bytes
@@ -317,7 +317,7 @@
os-2 ../../5.0/en/os-2
other-vendor-column-types other-vendor-data-types
parentheses operator-precedence
-partitioning-limitations-disallowed-functions partitioning-limitations-functions-disallowed 2009-01-01
+partitioning-limitations-disallowed-functions partitioning-limitations-functions 2009-01-01
partitioning-limitations-functions-supported partitioning-limitations-functions 2009-01-01
partitioning-limitations-functions-disallowed partitioning-limitations-functions 2009-01-01
performance myodbc-usagenotes-functionality
Modified: trunk/refman-5.2/renamed-nodes.txt
===================================================================
--- trunk/refman-5.2/renamed-nodes.txt 2007-12-12 19:26:14 UTC (rev 9269)
+++ trunk/refman-5.2/renamed-nodes.txt 2007-12-12 19:46:41 UTC (rev 9270)
Changed blocks: 1, Lines Added: 1, Lines Deleted: 1; 1327 bytes
@@ -3124,7 +3124,7 @@
partitioning-key http://dev.mysql.com/doc/refman/6.0/en/partitioning-key.html 2008-03-31
partitioning-limitations http://dev.mysql.com/doc/refman/6.0/en/partitioning-limitations.html 2008-03-31
partitioning-limitations-functions http://dev.mysql.com/doc/refman/6.0/en/partitioning-limitations-functions.html 2008-03-31
-partitioning-limitations-functions-disallowed http://dev.mysql.com/doc/refman/6.0/en/partitioning-limitations-functions-disallowed.html 2008-03-31
+partitioning-limitations-functions-disallowed http://dev.mysql.com/doc/refman/6.0/en/partitioning-limitations-functions.html 2008-03-31
partitioning-limitations-functions-supported http://dev.mysql.com/doc/refman/6.0/en/partitioning-limitations-functions-supported.html 2008-03-31
partitioning-limitations-partitioning-keys-unique-keys http://dev.mysql.com/doc/refman/6.0/en/partitioning-limitations-partitioning-keys-unique-keys.html 2008-03-31
partitioning-limitations-storage-engines http://dev.mysql.com/doc/refman/6.0/en/partitioning-limitations-storage-engines.html 2008-03-31
Modified: trunk/refman-6.0/Makefile.depends
===================================================================
--- trunk/refman-6.0/Makefile.depends 2007-12-12 19:26:14 UTC (rev 9269)
+++ trunk/refman-6.0/Makefile.depends 2007-12-12 19:46:41 UTC (rev 9270)
Changed blocks: 1, Lines Added: 1, Lines Deleted: 0; 490 bytes
@@ -705,6 +705,7 @@
metadata/dba-core.idmap \
metadata/installing-core.idmap \
metadata/introduction.idmap \
+ metadata/optimization.idmap \
metadata/programs-client.idmap \
metadata/se-falcon-core.idmap \
metadata/sql-syntax.idmap
| Thread |
|---|
| • svn commit - mysqldoc@docsrva: r9270 - in trunk: . dynamic-docs/changelog it/refman-5.1 pt/refman-5.1 refman-5.1 refman-5.2 refman-6.0 | paul | 12 Dec |
