MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:paul Date:March 24 2007 11:31pm
Subject:svn commit - mysqldoc@docsrva: r5551 - in trunk: . refman-4.1 refman-5.0 refman-5.1
View as plain text  
Author: paul
Date: 2007-03-25 00:31:01 +0100 (Sun, 25 Mar 2007)
New Revision: 5551

Log:
 r18192@frost:  paul | 2007-03-24 18:27:11 -0500
 Document extensions to index hint syntax. (WL#3527)
 Document old_mode system variable.
 Other minor revisions.


Modified:
   trunk/refman-4.1/sql-syntax.xml
   trunk/refman-5.0/sql-syntax.xml
   trunk/refman-5.1/database-administration.xml
   trunk/refman-5.1/news-5.1.xml
   trunk/refman-5.1/sql-syntax.xml

Property changes on: trunk
___________________________________________________________________
Name: svk:merge
   - 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:22159
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:18191
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:14593
   + 4767c598-dc10-0410-bea0-d01b485662eb:/mysqldoc-local/mysqldoc/trunk:22159
7d8d2c4e-af1d-0410-ab9f-b038ce55645b:/mysqldoc-local/mysqldoc:18192
b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:14218
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:14593


Modified: trunk/refman-4.1/sql-syntax.xml
===================================================================
--- trunk/refman-4.1/sql-syntax.xml	2007-03-24 23:30:25 UTC (rev 5550)
+++ trunk/refman-4.1/sql-syntax.xml	2007-03-24 23:31:01 UTC (rev 5551)
Changed blocks: 4, Lines Added: 22, Lines Deleted: 19; 3932 bytes

@@ -6549,8 +6549,8 @@
 
           <para>
             The use of index hints provides the optimizer with
-            information about how to choose indexes for retrieving rows
-            from a table. For a description of the syntax for specifying
+            information about how to choose indexes during query
+            processing. For a description of the syntax for specifying
             these hints, see <xref linkend="index-hints"/>.
           </para>
 

@@ -7609,11 +7609,11 @@
 
         <para>
           As of MySQL 3.23.12, you can provide hints to give the
-          optimizer information about how to choose indexes for
-          retrieving information from a table. <xref linkend="join"/>,
-          describes the general syntax for specifying tables in a
-          <literal>SELECT</literal> statement. For an individual table,
-          the syntax, including that for index hints, looks like this:
+          optimizer information about how to choose indexes during query
+          processing. <xref linkend="join"/>, describes the general
+          syntax for specifying tables in a <literal>SELECT</literal>
+          statement. The syntax for an individual able, including that
+          for index hints, looks like this:
         </para>
 
 <programlisting>

@@ -7631,12 +7631,12 @@
         <para>
           By specifying <literal>USE INDEX
           (<replaceable>index_list</replaceable>)</literal>, you can
-          tell MySQL to use only one of the possible indexes to find
-          rows in the table. The alternative syntax <literal>IGNORE
-          INDEX (<replaceable>index_list</replaceable>)</literal> can be
-          used to tell MySQL to not use some particular index. These
-          hints are useful if <literal>EXPLAIN</literal> shows that
-          MySQL is using the wrong index from the list of possible
+          tell MySQL to use only one of the named indexes to find rows
+          in the table. The alternative syntax <literal>IGNORE INDEX
+          (<replaceable>index_list</replaceable>)</literal> can be used
+          to tell MySQL to not use some particular index or indexes.
+          These hints are useful if <literal>EXPLAIN</literal> shows
+          that MySQL is using the wrong index from the list of possible
           indexes.
         </para>
 

@@ -7654,20 +7654,23 @@
           <literal>USE KEY</literal>, <literal>IGNORE KEY</literal>, and
           <literal>FORCE KEY</literal> are synonyms for <literal>USE
           INDEX</literal>, <literal>IGNORE INDEX</literal>, and
-          <literal>FORCE INDEX</literal>. Each hint requires the names
-          of <emphasis>indexes</emphasis>, not the names of columns. The
-          name of a <literal>PRIMARY KEY</literal> is
-          <literal>PRIMARY.</literal> Use <literal>SHOW INDEX</literal>
-          to see the index names for a table.
+          <literal>FORCE INDEX</literal>.
         </para>
 
         <para>
+          Each hint requires the names of <emphasis>indexes</emphasis>,
+          not the names of columns. The name of a <literal>PRIMARY
+          KEY</literal> is <literal>PRIMARY</literal>. To see the index
+          names for a table, use <literal>SHOW INDEX</literal>.
+        </para>
+
+        <para>
           <literal>USE INDEX</literal>, <literal>IGNORE INDEX</literal>,
           and <literal>FORCE INDEX</literal> affect only which indexes
           are used when MySQL decides how to find rows in the table and
           how to do the join. They do not affect whether an index is
           used when resolving an <literal>ORDER BY</literal> or
-          <literal>GROUP BY</literal>.
+          <literal>GROUP BY</literal> clause.
         </para>
 
         <para>


Modified: trunk/refman-5.0/sql-syntax.xml
===================================================================
--- trunk/refman-5.0/sql-syntax.xml	2007-03-24 23:30:25 UTC (rev 5550)
+++ trunk/refman-5.0/sql-syntax.xml	2007-03-24 23:31:01 UTC (rev 5551)
Changed blocks: 4, Lines Added: 19, Lines Deleted: 16; 3695 bytes

@@ -6600,8 +6600,8 @@
 
           <para>
             The use of index hints provides the optimizer with
-            information about how to choose indexes for retrieving rows
-            from a table. For a description of the syntax for specifying
+            information about how to choose indexes during query
+            processing. For a description of the syntax for specifying
             these hints, see <xref linkend="index-hints"/>.
           </para>
 

@@ -8296,10 +8296,10 @@
 
         <para>
           You can provide hints to give the optimizer information about
-          how to choose indexes for retrieving information from a table.
+          how to choose indexes during query processing.
           <xref linkend="join"/>, describes the general syntax for
           specifying tables in a <literal>SELECT</literal> statement.
-          For an individual table, the syntax, including that for index
+          The syntax for an individual able, including that for index
           hints, looks like this:
         </para>
 

@@ -8318,12 +8318,12 @@
         <para>
           By specifying <literal>USE INDEX
           (<replaceable>index_list</replaceable>)</literal>, you can
-          tell MySQL to use only one of the possible indexes to find
-          rows in the table. The alternative syntax <literal>IGNORE
-          INDEX (<replaceable>index_list</replaceable>)</literal> can be
-          used to tell MySQL to not use some particular index. These
-          hints are useful if <literal>EXPLAIN</literal> shows that
-          MySQL is using the wrong index from the list of possible
+          tell MySQL to use only one of the named indexes to find rows
+          in the table. The alternative syntax <literal>IGNORE INDEX
+          (<replaceable>index_list</replaceable>)</literal> can be used
+          to tell MySQL to not use some particular index or indexes.
+          These hints are useful if <literal>EXPLAIN</literal> shows
+          that MySQL is using the wrong index from the list of possible
           indexes.
         </para>
 

@@ -8341,20 +8341,23 @@
           <literal>USE KEY</literal>, <literal>IGNORE KEY</literal>, and
           <literal>FORCE KEY</literal> are synonyms for <literal>USE
           INDEX</literal>, <literal>IGNORE INDEX</literal>, and
-          <literal>FORCE INDEX</literal>. Each hint requires the names
-          of <emphasis>indexes</emphasis>, not the names of columns. The
-          name of a <literal>PRIMARY KEY</literal> is
-          <literal>PRIMARY.</literal> Use <literal>SHOW INDEX</literal>
-          to see the index names for a table.
+          <literal>FORCE INDEX</literal>.
         </para>
 
         <para>
+          Each hint requires the names of <emphasis>indexes</emphasis>,
+          not the names of columns. The name of a <literal>PRIMARY
+          KEY</literal> is <literal>PRIMARY</literal>. To see the index
+          names for a table, use <literal>SHOW INDEX</literal>.
+        </para>
+
+        <para>
           <literal>USE INDEX</literal>, <literal>IGNORE INDEX</literal>,
           and <literal>FORCE INDEX</literal> affect only which indexes
           are used when MySQL decides how to find rows in the table and
           how to do the join. They do not affect whether an index is
           used when resolving an <literal>ORDER BY</literal> or
-          <literal>GROUP BY</literal>.
+          <literal>GROUP BY</literal> clause.
         </para>
 
         <para>


Modified: trunk/refman-5.1/database-administration.xml
===================================================================
--- trunk/refman-5.1/database-administration.xml	2007-03-24 23:30:25 UTC (rev 5550)
+++ trunk/refman-5.1/database-administration.xml	2007-03-24 23:31:01 UTC (rev 5551)
Changed blocks: 1, Lines Added: 30, Lines Deleted: 0; 1710 bytes

@@ -6083,7 +6083,37 @@
           </listitem>
 
           <listitem>
+            <para id="optvar_old_mode">
+              <literal>old_mode</literal>
+            </para>
+
             <para>
+              <literal>old_mode</literal> is a compatibility variable.
+              It is disabled by default, but can be enabled at startup
+              to revert the server to behaviors present in older
+              versions.
+            </para>
+
+            <para>
+              Currently, when <literal>old_mode</literal> is enabled, it
+              changes the default scope of index hints to that used
+              prior to MySQL 5.1.17. That is, index hints with no
+              <literal>FOR</literal> clause apply only to how indexes
+              are used for row retrieval and not to resolution of
+              <literal>ORDER BY</literal> or <literal>GROUP BY</literal>
+              clauses. (See <xref linkend="index-hints"/>.) Take care
+              about enabling this in a replication setup. With
+              statement-based binary logging, having different modes for
+              the master and slaves might lead to replication errors.
+            </para>
+
+            <para>
+              This variable was added in MySQL 5.1.17.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para id="optvar_old_passwords">
               <literal>old_passwords</literal>
             </para>
 


Modified: trunk/refman-5.1/news-5.1.xml
===================================================================
--- trunk/refman-5.1/news-5.1.xml	2007-03-24 23:30:25 UTC (rev 5550)
+++ trunk/refman-5.1/news-5.1.xml	2007-03-24 23:31:01 UTC (rev 5551)
Changed blocks: 1, Lines Added: 18, Lines Deleted: 0; 1132 bytes

@@ -252,6 +252,24 @@
 
       <listitem>
         <para>
+          The syntax for index hints has been extended to enable more
+          fine-grained control over the optimizer's selection of an
+          execution plan for various phases of query processing. See
+          <xref linkend="index-hints"/>.
+        </para>
+      </listitem>
+
+      <listitem>
+        <para>
+          Added the <literal>old_mode</literal> system variable to cause
+          the server to revert to certain behaviors present in older
+          versions. Currently, this variable affects handling of index
+          hints. See <xref linkend="index-hints"/>.
+        </para>
+      </listitem>
+
+      <listitem>
+        <para>
           <literal>NDB Cluster</literal>: Added the
           <option>--skip-table-check</option> option (short form
           <option>-s</option>) for <command>ndb_restore</command>, which


Modified: trunk/refman-5.1/sql-syntax.xml
===================================================================
--- trunk/refman-5.1/sql-syntax.xml	2007-03-24 23:30:25 UTC (rev 5550)
+++ trunk/refman-5.1/sql-syntax.xml	2007-03-24 23:31:01 UTC (rev 5551)
Changed blocks: 7, Lines Added: 206, Lines Deleted: 29; 12001 bytes

@@ -8268,8 +8268,8 @@
 
           <para>
             The use of index hints provides the optimizer with
-            information about how to choose indexes for retrieving rows
-            from a table. For a description of the syntax for specifying
+            information about how to choose indexes during query
+            processing. For a description of the syntax for specifying
             these hints, see <xref linkend="index-hints"/>.
           </para>
 

@@ -9091,7 +9091,7 @@
   | <replaceable>join_table</replaceable>
 
 <replaceable>table_factor</replaceable>:
-    <replaceable>tbl_name</replaceable> [[AS] <replaceable>alias</replaceable>] [<replaceable>index_hint</replaceable>)]
+    <replaceable>tbl_name</replaceable> [[AS] <replaceable>alias</replaceable>] [<replaceable>index_hint_list</replaceable>)]
   | ( <replaceable>table_references</replaceable> )
   | { OJ <replaceable>table_reference</replaceable> LEFT OUTER JOIN <replaceable>table_reference</replaceable>
         ON <replaceable>conditional_expr</replaceable> }

@@ -9109,10 +9109,16 @@
     ON <replaceable>conditional_expr</replaceable>
   | USING (<replaceable>column_list</replaceable>)
 
+<replaceable>index_hint_list</replaceable>:
+    <replaceable>index_hint</replaceable> [, <replaceable>index_hint</replaceable>] ...
+
 <replaceable>index_hint</replaceable>:
-    USE {INDEX|KEY} (<replaceable>index_list</replaceable>)]
-  | IGNORE {INDEX|KEY} (<replaceable>index_list</replaceable>)]
-  | FORCE {INDEX|KEY} (<replaceable>index_list</replaceable>)]
+    USE {INDEX|KEY}
+      [{FOR {JOIN|ORDER BY|GROUP BY}] ([<replaceable>index_list</replaceable>])
+  | IGNORE {INDEX|KEY}
+      [{FOR {JOIN|ORDER BY|GROUP BY}] (<replaceable>index_list</replaceable>)
+  | FORCE {INDEX|KEY}
+      [{FOR {JOIN|ORDER BY|GROUP BY}] (<replaceable>index_list</replaceable>)
 
 <replaceable>index_list</replaceable>:
     <replaceable>index_name</replaceable> [, <replaceable>index_name</replaceable>] ...

@@ -9939,20 +9945,26 @@
 
         <para>
           You can provide hints to give the optimizer information about
-          how to choose indexes for retrieving information from a table.
+          how to choose indexes during query processing.
           <xref linkend="join"/>, describes the general syntax for
           specifying tables in a <literal>SELECT</literal> statement.
-          For an individual table, the syntax, including that for index
+          The syntax for an individual able, including that for index
           hints, looks like this:
         </para>
 
 <programlisting>
-<replaceable>tbl_name</replaceable> [[AS] <replaceable>alias</replaceable>] [<replaceable>index_hint</replaceable>)]
+<replaceable>tbl_name</replaceable> [[AS] <replaceable>alias</replaceable>] [<replaceable>index_hint_list</replaceable>)]
 
+<replaceable>index_hint_list</replaceable>:
+    <replaceable>index_hint</replaceable> [, <replaceable>index_hint</replaceable>] ...
+
 <replaceable>index_hint</replaceable>:
-    USE {INDEX|KEY} (<replaceable>index_list</replaceable>)]
-  | IGNORE {INDEX|KEY} (<replaceable>index_list</replaceable>)]
-  | FORCE {INDEX|KEY} (<replaceable>index_list</replaceable>)]
+    USE {INDEX|KEY}
+      [{FOR {JOIN|ORDER BY|GROUP BY}] ([<replaceable>index_list</replaceable>])
+  | IGNORE {INDEX|KEY}
+      [{FOR {JOIN|ORDER BY|GROUP BY}] (<replaceable>index_list</replaceable>)
+  | FORCE {INDEX|KEY}
+      [{FOR {JOIN|ORDER BY|GROUP BY}] (<replaceable>index_list</replaceable>)
 
 <replaceable>index_list</replaceable>:
     <replaceable>index_name</replaceable> [, <replaceable>index_name</replaceable>] ...

@@ -9961,12 +9973,12 @@
         <para>
           By specifying <literal>USE INDEX
           (<replaceable>index_list</replaceable>)</literal>, you can
-          tell MySQL to use only one of the possible indexes to find
-          rows in the table. The alternative syntax <literal>IGNORE
-          INDEX (<replaceable>index_list</replaceable>)</literal> can be
-          used to tell MySQL to not use some particular index. These
-          hints are useful if <literal>EXPLAIN</literal> shows that
-          MySQL is using the wrong index from the list of possible
+          tell MySQL to use only one of the named indexes to find rows
+          in the table. The alternative syntax <literal>IGNORE INDEX
+          (<replaceable>index_list</replaceable>)</literal> can be used
+          to tell MySQL to not use some particular index or indexes.
+          These hints are useful if <literal>EXPLAIN</literal> shows
+          that MySQL is using the wrong index from the list of possible
           indexes.
         </para>
 

@@ -9984,23 +9996,27 @@
           <literal>USE KEY</literal>, <literal>IGNORE KEY</literal>, and
           <literal>FORCE KEY</literal> are synonyms for <literal>USE
           INDEX</literal>, <literal>IGNORE INDEX</literal>, and
-          <literal>FORCE INDEX</literal>. Each hint requires the names
-          of <emphasis>indexes</emphasis>, not the names of columns. The
-          name of a <literal>PRIMARY KEY</literal> is
-          <literal>PRIMARY.</literal> Use <literal>SHOW INDEX</literal>
-          to see the index names for a table.
+          <literal>FORCE INDEX</literal>.
         </para>
 
         <para>
-          <literal>USE INDEX</literal>, <literal>IGNORE INDEX</literal>,
-          and <literal>FORCE INDEX</literal> affect only which indexes
-          are used when MySQL decides how to find rows in the table and
-          how to do the join. They do not affect whether an index is
-          used when resolving an <literal>ORDER BY</literal> or
-          <literal>GROUP BY</literal>.
+          Each hint requires the names of <emphasis>indexes</emphasis>,
+          not the names of columns. The name of a <literal>PRIMARY
+          KEY</literal> is <literal>PRIMARY</literal>. To see the index
+          names for a table, use <literal>SHOW INDEX</literal>.
         </para>
 
         <para>
+          Prior to MySQL 5.1.17, <literal>USE INDEX</literal>,
+          <literal>IGNORE INDEX</literal>, and <literal>FORCE
+          INDEX</literal> affect only which indexes are used when MySQL
+          decides how to find rows in the table and how to process
+          joins. They do not affect whether an index is used when
+          resolving an <literal>ORDER BY</literal> or <literal>GROUP
+          BY</literal> clause.
+        </para>
+
+        <para>
           Examples:
         </para>
 

@@ -10012,6 +10028,167 @@
   WHERE col1=1 AND col2=2 AND col3=3;
 </programlisting>
 
+        <para>
+          As of MySQL 5.1.17, the syntax for index hints is extended in
+          the following ways:
+        </para>
+
+        <itemizedlist>
+
+          <listitem>
+            <para>
+              It is syntactically valid to specify an empty
+              <replaceable>index_list</replaceable> for <literal>USE
+              INDEX</literal>, which means <quote>use no
+              indexes.</quote> Specifying an empty
+              <replaceable>index_list</replaceable> for <literal>FORCE
+              INDEX</literal> or <literal>IGNORE INDEX</literal> is a
+              syntax error.
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You can specify the scope of a index hint by adding a
+              <literal>FOR</literal> clause to the hint. This provides
+              more fine-grained control over the optimizer's selection
+              of an execution plan for various phases of query
+              processing. To affect only the indexes used when MySQL
+              decides how to find rows in the table and how to process
+              joins, use <literal>FOR JOIN</literal>. To influence index
+              usage for sorting or grouping rows, use <literal>FOR ORDER
+              BY</literal> or <literal>FOR GROUP BY</literal>. (However,
+              if there is a covering index for the table and it is used
+              to access the table, the optimizer will ignore
+              <literal>IGNORE INDEX FOR {ORDER BY|GROUP BY}</literal>
+              hints that disable that index.)
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              You can specify multiple index hints:
+            </para>
+
+<programlisting>
+SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX FOR ORDER BY (i2) ORDER BY a;
+</programlisting>
+
+            <para>
+              It is not a error to name the same index in several hints
+              (even within the same hint):
+            </para>
+
+<programlisting>
+SELECT * FROM t1 USE INDEX (i1) USE INDEX (i1,i1);
+</programlisting>
+
+            <para>
+              However, it is an error to mix <literal>USE
+              INDEX</literal> and <literal>FORCE INDEX</literal> for the
+              same table:
+            </para>
+
+<programlisting>
+SELECT * FROM t1 USE INDEX FOR JOIN (i1) FORCE INDEX FOR JOIN (i2);
+</programlisting>
+          </listitem>
+
+        </itemizedlist>
+
+        <para>
+          The default scope of index hints also is changed as of MySQL
+          5.1.17. Formerly, index hints applied only to how indexes are
+          used for retrieval of records and not during resolution of
+          <literal>ORDER BY</literal> or <literal>GROUP BY</literal>
+          clauses. As of 5.1.17, if you specify no
+          <literal>FOR</literal> clause for an index hint, the hint by
+          default applies to all parts of the statement. For example,
+          this hint:
+        </para>
+
+<programlisting>
+IGNORE INDEX (i1)
+</programlisting>
+
+        <para>
+          is equivalent to this combination of hints:
+        </para>
+
+<programlisting>
+IGNORE INDEX FOR JOIN (i1)
+IGNORE INDEX FOR ORDER BY (i1)
+IGNORE INDEX FOR GROUP BY (i1)
+</programlisting>
+
+        <para>
+          To cause the server to use the older behavior for hint scope
+          when no <literal>FOR</literal> clause is present (so that
+          hints apply only to row retrieval), enable the
+          <literal>old_mode</literal> system variable at server startup.
+          Take care about enabling this variable in a replication setup.
+          With statement-based binary logging, having different modes
+          for the master and slaves might lead to replication errors.
+        </para>
+
+        <para>
+          When index hints are processed, they are are collected in a
+          single list by type (<literal>USE</literal>,
+          <literal>FORCE</literal>, <literal>IGNORE</literal>) and by
+          scope (<literal>FOR JOIN</literal>, <literal>FOR ORDER
+          BY</literal>, <literal>FOR GROUP BY</literal>). For example:
+        </para>
+
+<programlisting>
+SELECT * FROM t1
+  USE INDEX () IGNORE INDEX (i2) USE INDEX (i1) USE INDEX (i2);
+</programlisting>
+
+        <para>
+          is equivalent to:
+        </para>
+
+<programlisting>
+SELECT * FROM t1
+   USE INDEX (i1,i2) IGNORE INDEX (i2);
+</programlisting>
+
+        <para>
+          The index hints then are applied for each scope in the
+          following order:
+        </para>
+
+        <orderedlist>
+
+          <listitem>
+            <para>
+              <literal>{USE|FORCE} INDEX</literal> is applied if
+              present. (If not, the optimizer-determined set of indexes
+              is used.)
+            </para>
+          </listitem>
+
+          <listitem>
+            <para>
+              <literal>IGNORE INDEX</literal> is applied over the result
+              of the previous step. For example:
+            </para>
+
+<programlisting>
+SELECT * FROM t1 USE INDEX (i1) IGNORE INDEX (i2) USE INDEX (i2)
+</programlisting>
+
+            <para>
+              is equivalent to:
+            </para>
+
+<programlisting>
+SELECT * FROM t1 USE INDEX (i1).
+</programlisting>
+          </listitem>
+
+        </orderedlist>
+
       </section>
 
       <section id="union">


Thread
svn commit - mysqldoc@docsrva: r5551 - in trunk: . refman-4.1 refman-5.0 refman-5.1paul25 Mar