List:Commits« Previous MessageNext Message »
From:paul Date:January 16 2006 9:43pm
Subject:svn commit - mysqldoc@docsrva: r859 - in trunk: . refman-4.1 refman-5.0 refman-5.1
View as plain text  
Author: paul
Date: 2006-01-16 22:42:59 +0100 (Mon, 16 Jan 2006)
New Revision: 859

Log:
 r2229@kite-hub:  paul | 2006-01-16 15:42:39 -0600
 General revisions.


Modified:
   trunk/
   trunk/refman-4.1/optimization.xml
   trunk/refman-5.0/optimization.xml
   trunk/refman-5.1/optimization.xml


Property changes on: trunk
___________________________________________________________________
Name: svk:merge
   - b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6255
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2225
   + b5ec3a16-e900-0410-9ad2-d183a3acac99:/mysqldoc-local/mysqldoc/trunk:6255
bf112a9c-6c03-0410-a055-ad865cd57414:/mysqldoc-local/mysqldoc/trunk:2229

Modified: trunk/refman-4.1/optimization.xml
===================================================================
--- trunk/refman-4.1/optimization.xml	2006-01-16 21:11:46 UTC (rev 858)
+++ trunk/refman-4.1/optimization.xml	2006-01-16 21:42:59 UTC (rev 859)
@@ -1332,7 +1332,7 @@
 
               <para>
                 MySQL found no good index to use, but found that some of
-                indexes might be used once column values from preceding
+                indexes might be used after column values from preceding
                 tables are known. For each row combination in the
                 preceding tables, MySQL checks whether it is possible to
                 use a <literal>range</literal> access method to retrieve
@@ -3351,14 +3351,14 @@
         </para>
 
         <para>
-          For this method to work, it is sufficient that, for all
-          columns in a query referring to parts of the key coming before
-          or in between parts of the <literal>GROUP BY</literal> key,
-          there is a constant equality condition. The constants from the
-          equality conditions fill in any <quote>gaps</quote> in the
-          search keys so that it is possible to form complete prefixes
-          of the index. These index prefixes can be then used for index
-          lookups. If we require sorting of the <literal>GROUP
+          For this method to work, it is sufficient that there is a
+          constant equality condition for all columns in a query
+          referring to parts of the key coming before or in between
+          parts of the <literal>GROUP BY</literal> key. The constants
+          from the equality conditions fill in any <quote>gaps</quote>
+          in the search keys so that it is possible to form complete
+          prefixes of the index. These index prefixes then can be used
+          for index lookups. If we require sorting of the <literal>GROUP
           BY</literal> result, and it is possible to form search keys
           that are prefixes of the index, MySQL also avoids extra
           sorting operations because searching with prefixes in an
@@ -3378,7 +3378,7 @@
           <listitem>
             <para>
               There is a gap in the <literal>GROUP BY</literal>, but it
-              is covered by the condition <literal>c2 = 'a'</literal>.
+              is covered by the condition <literal>c2 = 'a'</literal>:
             </para>
 
 <programlisting>
@@ -3447,9 +3447,9 @@
             <literal>LIMIT</literal> clause must be selected, and most
             or all of them must be sorted, before it can be ascertained
             that the first <replaceable>row_count</replaceable> rows
-            have been found. In either case, once the rows have been
-            found, there is no need to sort any remainder of the result
-            set, and MySQL does not do so.
+            have been found. In either case, after the initial rows have
+            been found, there is no need to sort any remainder of the
+            result set, and MySQL does not do so.
           </para>
         </listitem>
 
@@ -3498,9 +3498,9 @@
         <listitem>
           <para>
             When the server uses temporary tables to resolve the query,
-            the <literal>LIMIT
-            <replaceable>row_count</replaceable></literal> clause is
-            used to calculate how much space is required.
+            it uses the <literal>LIMIT
+            <replaceable>row_count</replaceable></literal> clause to
+            calculate how much space is required.
           </para>
         </listitem>
 
@@ -3529,8 +3529,8 @@
         <listitem>
           <para>
             The table is so small that it is faster to perform a table
-            scan than a key lookup. This is common for tables with fewer
-            than 10 rows and a short row length.
+            scan than to bother with a key lookup. This is common for
+            tables with fewer than 10 rows and a short row length.
           </para>
         </listitem>
 
@@ -3556,7 +3556,7 @@
           <para>
             You are using a key with low cardinality (many rows match
             the key value) through another column. In this case, MySQL
-            assumes that by using the key it probably does a lot of key
+            assumes that by using the key it probably will do many key
             lookups and that a table scan would be faster.
           </para>
         </listitem>
@@ -3564,9 +3564,10 @@
       </itemizedlist>
 
       <para>
-        For small tables, a table scan often is appropriate. For large
-        tables, try the following techniques to avoid having the
-        optimizer incorrectly choose a table scan:
+        For small tables, a table scan often is appropriate and the
+        performance impact is negligible. For large tables, try the
+        following techniques to avoid having the optimizer incorrectly
+        choose a table scan:
       </para>
 
       <itemizedlist>
@@ -3584,13 +3585,17 @@
           <para>
             Use <literal>FORCE INDEX</literal> for the scanned table to
             tell MySQL that table scans are very expensive compared to
-            using the given index. See <xref linkend="select"/>.
+            using the given index:
           </para>
 
 <programlisting>
 SELECT * FROM t1, t2 FORCE INDEX (<replaceable>index_for_column</replaceable>)
   WHERE t1.<replaceable>col_name</replaceable>=t2.<replaceable>col_name</replaceable>;
 </programlisting>
+
+          <para>
+            See <xref linkend="select"/>.
+          </para>
         </listitem>
 
         <listitem>
@@ -3653,13 +3658,13 @@
 
         <listitem>
           <para>
-            Inserting row: (1 x size of row)
+            Inserting row: (1 &times; size of row)
           </para>
         </listitem>
 
         <listitem>
           <para>
-            Inserting indexes: (1 x number of indexes)
+            Inserting indexes: (1 &times; number of indexes)
           </para>
         </listitem>
 
@@ -3696,7 +3701,7 @@
             rows at a time. This is considerably faster (many times
             faster in some cases) than using separate single-row
             <literal>INSERT</literal> statements. If you are adding data
-            to a non-empty table, you may tune the
+            to a non-empty table, you can tune the
             <literal>bulk_insert_buffer_size</literal> variable to make
             data insertion even faster. See
             <xref linkend="server-system-variables"/>.
@@ -3707,15 +3712,26 @@
           <para>
             If you are inserting a lot of rows from different clients,
             you can get higher speed by using the <literal>INSERT
-            DELAYED</literal> statement. See <xref linkend="insert"/>.
+            DELAYED</literal> statement. See
+            <xref linkend="insert-delayed"/>.
           </para>
         </listitem>
 
         <listitem>
           <para>
-            With <literal>MyISAM</literal> tables you can insert rows at
-            the same time that <literal>SELECT</literal> statements are
-            running if there are no deleted rows in the tables.
+            <indexterm>
+              <primary>concurrent inserts</primary>
+            </indexterm>
+
+            <indexterm>
+              <primary>inserts</primary>
+              <secondary>concurrent</secondary>
+            </indexterm>
+
+            For a <literal>MyISAM</literal> table, you can use
+            concurrent inserts to add rows at the same time that
+            <literal>SELECT</literal> statements are running if there
+            are no deleted rows in middle of the table.
           </para>
         </listitem>
 
@@ -3723,7 +3739,7 @@
           <para>
             When loading a table from a text file, use <literal>LOAD
             DATA INFILE</literal>. This is usually 20 times faster than
-            using a lot of <literal>INSERT</literal> statements. See
+            using <literal>INSERT</literal> statements. See
             <xref linkend="load-data"/>.
           </para>
         </listitem>
@@ -3731,8 +3747,9 @@
         <listitem>
           <para>
             With some extra work, it is possible to make <literal>LOAD
-            DATA INFILE</literal> run even faster when the table has
-            many indexes. Use the following procedure:
+            DATA INFILE</literal> run even faster for a
+            <literal>MyISAM</literal> table when the table has many
+            indexes. Use the following procedure:
           </para>
 
           <orderedlist>
@@ -3777,12 +3794,13 @@
 
             <listitem>
               <para>
-                Re-create the indexes with <command>myisamchk -r -q
+                Re-create the indexes with <command>myisamchk -rq
                 <replaceable>/path/to/db/tbl_name</replaceable></command>.
                 This creates the index tree in memory before writing it
-                to disk, which is much faster because it avoids lots of
-                disk seeks. The resulting index tree is also perfectly
-                balanced.
+                to disk, which is much faster that updating the index
+                during <literal>LOAD DATA INFILE</literal> because it
+                avoids lots of disk seeks. The resulting index tree is
+                also perfectly balanced.
               </para>
             </listitem>
 
@@ -3796,33 +3814,34 @@
           </orderedlist>
 
           <para>
-            Note that <literal>LOAD DATA INFILE</literal> also performs
-            the preceding optimization if you insert into an empty
-            <literal>MyISAM</literal> table; the main difference is that
-            you can let <command>myisamchk</command> allocate much more
-            temporary memory for the index creation than you might want
-            the server to allocate for index re-creation when it
-            executes the <literal>LOAD DATA INFILE</literal> statement.
+            Note that <literal>LOAD DATA INFILE</literal> performs the
+            preceding optimization automatically if the
+            <literal>MyISAM</literal> table into which you insert data
+            is empty. The main difference is that you can let
+            <command>myisamchk</command> allocate much more temporary
+            memory for the index creation than you might want the server
+            to allocate for index re-creation when it executes the
+            <literal>LOAD DATA INFILE</literal> statement.
           </para>
 
           <para>
-            As of MySQL 4.0, you can also use <literal>ALTER TABLE
-            <replaceable>tbl_name</replaceable> DISABLE KEYS</literal>
-            instead of <command>myisamchk --keys-used=0 -rq
-            <replaceable>/path/to/db/tbl_name</replaceable></command>
-            and <literal>ALTER TABLE <replaceable>tbl_name</replaceable>
-            ENABLE KEYS</literal> instead of <command>myisamchk -r -q
-            <replaceable>/path/to/db/tbl_name</replaceable></command>.
-            In this way, you can also skip the <literal>FLUSH
-            TABLES</literal> steps.
+            As of MySQL 4.0, you can also disable or enable the indexes
+            for a <literal>MyISAM</literal> table by using the following
+            statements rather than <command>myisamchk</command>. If you
+            use these statements, you can skip the <literal>FLUSH
+            TABLE</literal> operations:
           </para>
+
+<programlisting>
+ALTER TABLE <replaceable>tbl_name</replaceable> DISABLE KEYS;
+ALTER TABLE <replaceable>tbl_name</replaceable> ENABLE KEYS;
+</programlisting>
         </listitem>
 
         <listitem>
           <para>
-            You can speed up <literal>INSERT</literal> operations that
-            are performed with multiple statements by locking your
-            tables:
+            To speed up <literal>INSERT</literal> operations that are
+            performed with multiple statements, lock your tables:
           </para>
 
 <programlisting>
@@ -3836,16 +3855,16 @@
             This benefits performance because the index buffer is
             flushed to disk only once, after all
             <literal>INSERT</literal> statements have completed.
-            Normally there would be as many index buffer flushes as
+            Normally, there would be as many index buffer flushes as
             there are <literal>INSERT</literal> statements. Explicit
             locking statements are not needed if you can insert all rows
-            with a single statement.
+            with a single <literal>INSERT</literal>.
           </para>
 
           <para>
-            For transactional tables, you should use
-            <literal>BEGIN</literal> and <literal>COMMIT</literal>
-            instead of <literal>LOCK TABLES</literal> to obtain faster
+            For transactional tables, you should use <literal>START
+            TRANSACTION</literal> and <literal>COMMIT</literal> instead
+            of <literal>LOCK TABLES</literal> to obtain faster
             insertions.
           </para>
 
@@ -3856,12 +3875,28 @@
             example:
           </para>
 
-<programlisting>
-Connection 1 does 1000 inserts
-Connections 2, 3, and 4 do 1 insert
-Connection 5 does 1000 inserts
-</programlisting>
+          <orderedlist>
 
+            <listitem>
+              <para>
+                Connection 1 does 1000 inserts
+              </para>
+            </listitem>
+
+            <listitem>
+              <para>
+                Connections 2, 3, and 4 do 1 insert
+              </para>
+            </listitem>
+
+            <listitem>
+              <para>
+                Connection 5 does 1000 inserts
+              </para>
+            </listitem>
+
+          </orderedlist>
+
           <para>
             If you do not use locking, connections 2, 3, and 4 finish
             before 1 and 5. If you use locking, connections 2, 3, and 4
@@ -3891,8 +3926,8 @@
 
         <listitem>
           <para>
-            To get some more speed for <literal>MyISAM</literal> tables,
-            for both <literal>LOAD DATA INFILE</literal> and
+            To increase performance for <literal>MyISAM</literal>
+            tables, for both <literal>LOAD DATA INFILE</literal> and
             <literal>INSERT</literal>, enlarge the key cache by
             increasing the <literal>key_buffer_size</literal> system
             variable. See <xref linkend="server-parameters"/>.
@@ -3916,16 +3951,16 @@
       </para>
 
       <para>
-        Also, another way to get fast updates is to delay updates and
-        then do many updates in a row later. Performing multiple updates
+        Another way to get fast updates is to delay updates and then do
+        many updates in a row later. Performing multiple updates
         together is much quicker than doing one at a time if you lock
         the table.
       </para>
 
       <para>
-        Note that for a <literal>MyISAM</literal> table that uses
-        dynamic row format, updating a row to a longer total length may
-        split the row. If you do this often, it is very important to use
+        For a <literal>MyISAM</literal> table that uses dynamic row
+        format, updating a row to a longer total length may split the
+        row. If you do this often, it is very important to use
         <literal>OPTIMIZE TABLE</literal> occasionally. See
         <xref linkend="optimize-table"/>.
       </para>
@@ -3939,15 +3974,15 @@
       <para>
         The time required to delete individual rows is exactly
         proportional to the number of indexes. To delete rows more
-        quickly, you can increase the size of the key cache. See
-        <xref linkend="server-parameters"/>.
+        quickly, you can increase the size of the key cache by
+        increasing the <literal>key_buffer_size</literal> system
+        variable. See <xref linkend="server-parameters"/>.
       </para>
 
       <para>
-        If you want to delete all rows from a table, use
-        <literal>TRUNCATE TABLE
-        <replaceable>tbl_name</replaceable></literal> rather than
-        <literal>DELETE FROM
+        To delete all rows from a table, <literal>TRUNCATE TABLE
+        <replaceable>tbl_name</replaceable></literal> if faster than
+        than <literal>DELETE FROM
         <replaceable>tbl_name</replaceable></literal>. See
         <xref linkend="truncate"/>.
       </para>
@@ -3995,8 +4030,8 @@
         <listitem>
           <para>
             Always check whether all your queries really use the indexes
-            you have created in the tables. In MySQL, you can do this
-            with the <literal>EXPLAIN</literal> statement. See
+            that you have created in the tables. In MySQL, you can do
+            this with the <literal>EXPLAIN</literal> statement. See
             <xref linkend="explain"/>.
           </para>
         </listitem>
@@ -4013,19 +4048,20 @@
         <listitem>
           <para>
             With <literal>MyISAM</literal> tables that have no deleted
-            rows, you can insert rows at the end at the same time that
-            another query is reading from the table. If this is
-            important for you, you should consider using the table in
-            ways that avoid deleting rows. Another possibility is to run
-            <literal>OPTIMIZE TABLE</literal> after you have deleted a
-            lot of rows. See <xref linkend="myisam-storage-engine"/>.
+            rows in the middle, you can insert rows at the end at the
+            same time that another query is reading from the table. If
+            it is important to be able to do this, you should consider
+            using the table in ways that avoid deleting rows. Another
+            possibility is to run <literal>OPTIMIZE TABLE</literal> to
+            defragment the table after you have deleted a lot of rows
+            from it. See <xref linkend="myisam-storage-engine"/>.
           </para>
         </listitem>
 
         <listitem>
           <para>
             To fix any compression issues that may have occurred with
-            <literal>ARCHIVE</literal> tables you can always do an
+            <literal>ARCHIVE</literal> tables, you can use
             <literal>OPTIMIZE TABLE</literal>. See
             <xref linkend="archive-storage-engine"/>.
           </para>
@@ -4036,7 +4072,7 @@
             Use <literal>ALTER TABLE &hellip; ORDER BY
             <replaceable>expr1</replaceable>,
             <replaceable>expr2</replaceable>, &hellip;</literal> if you
-            mostly retrieve rows in
+            usually retrieve rows in
             <literal><replaceable>expr1</replaceable>,
             <replaceable>expr2</replaceable>, &hellip;</literal> order.
             By using this option after extensive changes to the table,
@@ -4049,8 +4085,8 @@
             In some cases, it may make sense to introduce a column that
             is <quote>hashed</quote> based on information from other
             columns. If this column is short and reasonably unique, it
-            may be much faster than a big index on many columns. In
-            MySQL, it is very easy to use this extra column:
+            may be much faster than a <quote>wide</quote> index on many
+            columns. In MySQL, it is very easy to use this extra column:
           </para>
 
 <programlisting>
@@ -4100,12 +4136,12 @@
 </programlisting>
 
           <para>
-            This is really important when you use MySQL storage engines
+            This is very important when you use MySQL storage engines
             such as <literal>MyISAM</literal> and
             <literal>ISAM</literal> that have only table-level locking
             (multiple readers with single writers). This also gives
-            better performance with most databases, because the row
-            locking manager in this case has less to do.
+            better performance with most database systems, because the
+            row locking manager in this case has less to do.
           </para>
         </listitem>
 
@@ -4136,7 +4172,7 @@
             Take advantage of the fact that columns have default values.
             Insert values explicitly only when the value to be inserted
             differs from the default. This reduces the parsing that
-            MySQL needs to do and improves the insert speed.
+            MySQL must do and improves the insert speed.
           </para>
         </listitem>
 
@@ -4164,11 +4200,9 @@
 
         <listitem>
           <para>
-            Stored procedures or UDFs (user-defined functions) may be a
-            good way to get more performance for some tasks. However, if
-            you use a database system that does not support these
-            capabilities, you should always have another way to perform
-            the same tasks, even if the alternative method is slower.
+            UDFs (user-defined functions) may be a good way to get more
+            performance for some tasks. See
+            <xref linkend="adding-functions"/>, for more information.
           </para>
         </listitem>
 
@@ -4176,19 +4210,20 @@
           <para>
             You can always gain something by caching queries or answers
             in your application and then performing many inserts or
-            updates together. If your database supports table locks
-            (like 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"/>.
+            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"/>.
           </para>
         </listitem>
 
         <listitem>
           <para>
             Use <literal>INSERT DELAYED</literal> when you do not need
-            to know when your data is written. This speeds things up
-            because many rows can be written with a single disk write.
+            to know when your data is written. This reduces the overall
+            insertion impact because many rows can be written with a
+            single disk write.
           </para>
         </listitem>
 
@@ -4212,8 +4247,8 @@
         <listitem>
           <para>
             Use multiple-row <literal>INSERT</literal> statements to
-            store many rows with one SQL statement (many SQL servers
-            support this, including MySQL).
+            store many rows with one SQL statement. Many SQL servers
+            support this, including MySQL.
           </para>
         </listitem>
 
@@ -4235,8 +4270,8 @@
         <listitem>
           <para>
             Use <literal>OPTIMIZE TABLE</literal> once in a while to
-            avoid fragmentation with <literal>MyISAM</literal> tables
-            when using a dynamic table format. See
+            avoid fragmentation with dynamic-format
+            <literal>MyISAM</literal> tables. See
             <xref linkend="myisam-table-formats"/>.
           </para>
         </listitem>
@@ -4246,6 +4281,12 @@
             Use <literal>MEMORY</literal> (<literal>HEAP</literal>)
             tables when possible to get more speed. See
             <xref linkend="memory-storage-engine"/>.
+            <literal>MEMORY</literal> tables are useful for non-critical
+            data that is accessed often, such as information about the
+            last displayed banner for users who don't have cookies
+            enabled in their Web browser. User sessions are another
+            alternative available in many Web application environments
+            for handling volatile state data.
           </para>
         </listitem>
 
@@ -4261,20 +4302,10 @@
 
         <listitem>
           <para>
-            Use in-memory tables for non-critical data that is accessed
-            often, such as information about the last displayed banner
-            for users who don't have cookies enabled in their Web
-            browser. User sessions are another alternative available in
-            many Web application environments for handling volatile
-            state data.
-          </para>
-        </listitem>
-
-        <listitem>
-          <para>
             Columns with identical information in different tables
-            should be declared to have identical data types. Before
-            MySQL 3.23, you get slow joins otherwise.
+            should be declared to have identical data types so that
+            joins based on the corresponding columns will be faster.
+            Before MySQL 3.23, you get slow joins otherwise.
           </para>
 
           <para>
@@ -4323,7 +4354,7 @@
             database is likely to be stored in a more compact format
             than in the text file, so accessing it involves fewer disk
             accesses. You also save code in your application because you
-            do not have to parse your text files to find line and column
+            need not parse your text files to find line and column
             boundaries.
           </para>
         </listitem>
@@ -4345,7 +4376,7 @@
             index updates faster because they are not flushed to disk
             until the table is closed. The downside is that if something
             kills the server while such a table is open, you should
-            ensure that they are okay by running the server with the
+            ensure that the table is okay by running the server with the
             <option>--myisam-recover</option> option, or by running
             <command>myisamchk</command> before restarting the server.
             (However, even in this case, you should not lose anything by
@@ -4409,11 +4440,11 @@
       </indexterm>
 
       <para>
-        Currently, MySQL supports table-level locking for
-        <literal>ISAM</literal>, <literal>MyISAM</literal>, and
-        <literal>MEMORY</literal> (<literal>HEAP</literal>) tables,
-        page-level locking for <literal>BDB</literal> tables, and
-        row-level locking for <literal>InnoDB</literal> tables.
+        MySQL uses table-level locking for <literal>ISAM</literal>,
+        <literal>MyISAM</literal>, and <literal>MEMORY</literal>
+        (<literal>HEAP</literal>) tables, page-level locking for
+        <literal>BDB</literal> tables, and row-level locking for
+        <literal>InnoDB</literal> tables.
       </para>
 
       <para>
@@ -4486,17 +4517,13 @@
 
       <para>
         When a lock is released, the lock is made available to the
-        threads in the write lock queue, and then to the threads in the
-        read lock queue.
+        threads in the write lock queue and then to the threads in the
+        read lock queue. This means that if you have many updates for a
+        table, <literal>SELECT</literal> statements wait until there are
+        no more updates.
       </para>
 
       <para>
-        This means that if you have many updates for a table,
-        <literal>SELECT</literal> statements wait until there are no
-        more updates.
-      </para>
-
-      <para>
         Starting in MySQL 3.23.33, you can analyze the table lock
         contention on your system by checking the
         <literal>Table_locks_waited</literal> and
@@ -4517,20 +4544,24 @@
         <primary>concurrent inserts</primary>
       </indexterm>
 
+      <indexterm>
+        <primary>inserts</primary>
+        <secondary>concurrent</secondary>
+      </indexterm>
+
       <para>
-        As of MySQL 3.23.7 (3.23.25 for Windows), you can freely mix
-        concurrent <literal>INSERT</literal> and
-        <literal>SELECT</literal> statements for a
-        <literal>MyISAM</literal> table without locks if the
-        <literal>INSERT</literal> statements are non-conflicting. That
-        is, you can insert rows into a <literal>MyISAM</literal> table
-        at the same time other clients are reading from it. No conflict
-        occurs if the data file contains no free blocks in the middle,
-        because in that case, rows always are inserted at the end of the
-        data file. (Holes can result from rows having been deleted from
-        or updated in the middle of the table.) If there are holes,
-        concurrent inserts are re-enabled automatically when all holes
-        have been filled with new data.
+        As of MySQL 3.23.7 (3.23.25 for Windows), if a
+        <literal>MyISAM</literal> table contains no free blocks in the
+        middle, rows always are inserted at the end of the data file. In
+        this case, you can freely mix concurrent
+        <literal>INSERT</literal> and <literal>SELECT</literal>
+        statements for a <literal>MyISAM</literal> table without locks.
+        That is, you can insert rows into a <literal>MyISAM</literal>
+        table at the same time other clients are reading from it. (Holes
+        can result from rows having been deleted from or updated in the
+        middle of the table. If there are holes, concurrent inserts are
+        disabled but are re-enabled automatically when all holes have
+        been filled with new data.)
       </para>
 
       <para>
@@ -6392,7 +6423,7 @@
           Instead, the server directly accesses the table indexes using
           native filesystem caching. Filesystem caching is not as
           efficient as using a key cache, so although queries execute, a
-          slowdown can be anticipated. Once the cache has been
+          slowdown can be anticipated. After the cache has been
           restructured, it becomes available again for caching indexes
           assigned to it, and the use of filesystem caching for the
           indexes ceases.

Modified: trunk/refman-5.0/optimization.xml
===================================================================
--- trunk/refman-5.0/optimization.xml	2006-01-16 21:11:46 UTC (rev 858)
+++ trunk/refman-5.0/optimization.xml	2006-01-16 21:42:59 UTC (rev 859)
@@ -1341,7 +1341,7 @@
 
               <para>
                 MySQL found no good index to use, but found that some of
-                indexes might be used once column values from preceding
+                indexes might be used after column values from preceding
                 tables are known. For each row combination in the
                 preceding tables, MySQL checks whether it is possible to
                 use a <literal>range</literal> or
@@ -4802,14 +4802,14 @@
         </para>
 
         <para>
-          For this method to work, it is sufficient that, for all
-          columns in a query referring to parts of the key coming before
-          or in between parts of the <literal>GROUP BY</literal> key,
-          there is a constant equality condition. The constants from the
-          equality conditions fill in any <quote>gaps</quote> in the
-          search keys so that it is possible to form complete prefixes
-          of the index. These index prefixes can be then used for index
-          lookups. If we require sorting of the <literal>GROUP
+          For this method to work, it is sufficient that there is a
+          constant equality condition for all columns in a query
+          referring to parts of the key coming before or in between
+          parts of the <literal>GROUP BY</literal> key. The constants
+          from the equality conditions fill in any <quote>gaps</quote>
+          in the search keys so that it is possible to form complete
+          prefixes of the index. These index prefixes then can be used
+          for index lookups. If we require sorting of the <literal>GROUP
           BY</literal> result, and it is possible to form search keys
           that are prefixes of the index, MySQL also avoids extra
           sorting operations because searching with prefixes in an
@@ -4829,7 +4829,7 @@
           <listitem>
             <para>
               There is a gap in the <literal>GROUP BY</literal>, but it
-              is covered by the condition <literal>c2 = 'a'</literal>.
+              is covered by the condition <literal>c2 = 'a'</literal>:
             </para>
 
 <programlisting>
@@ -4898,9 +4898,9 @@
             <literal>LIMIT</literal> clause must be selected, and most
             or all of them must be sorted, before it can be ascertained
             that the first <replaceable>row_count</replaceable> rows
-            have been found. In either case, once the rows have been
-            found, there is no need to sort any remainder of the result
-            set, and MySQL does not do so.
+            have been found. In either case, after the initial rows have
+            been found, there is no need to sort any remainder of the
+            result set, and MySQL does not do so.
           </para>
         </listitem>
 
@@ -4949,9 +4949,9 @@
         <listitem>
           <para>
             When the server uses temporary tables to resolve the query,
-            the <literal>LIMIT
-            <replaceable>row_count</replaceable></literal> clause is
-            used to calculate how much space is required.
+            it uses the <literal>LIMIT
+            <replaceable>row_count</replaceable></literal> clause to
+            calculate how much space is required.
           </para>
         </listitem>
 
@@ -4980,8 +4980,8 @@
         <listitem>
           <para>
             The table is so small that it is faster to perform a table
-            scan than a key lookup. This is common for tables with fewer
-            than 10 rows and a short row length.
+            scan than to bother with a key lookup. This is common for
+            tables with fewer than 10 rows and a short row length.
           </para>
         </listitem>
 
@@ -5007,7 +5007,7 @@
           <para>
             You are using a key with low cardinality (many rows match
             the key value) through another column. In this case, MySQL
-            assumes that by using the key it probably does a lot of key
+            assumes that by using the key it probably will do many key
             lookups and that a table scan would be faster.
           </para>
         </listitem>
@@ -5015,9 +5015,10 @@
       </itemizedlist>
 
       <para>
-        For small tables, a table scan often is appropriate. For large
-        tables, try the following techniques to avoid having the
-        optimizer incorrectly choose a table scan:
+        For small tables, a table scan often is appropriate and the
+        performance impact is negligible. For large tables, try the
+        following techniques to avoid having the optimizer incorrectly
+        choose a table scan:
       </para>
 
       <itemizedlist>
@@ -5035,13 +5036,17 @@
           <para>
             Use <literal>FORCE INDEX</literal> for the scanned table to
             tell MySQL that table scans are very expensive compared to
-            using the given index. See <xref linkend="select"/>.
+            using the given index:
           </para>
 
 <programlisting>
 SELECT * FROM t1, t2 FORCE INDEX (<replaceable>index_for_column</replaceable>)
   WHERE t1.<replaceable>col_name</replaceable>=t2.<replaceable>col_name</replaceable>;
 </programlisting>
+
+          <para>
+            See <xref linkend="select"/>.
+          </para>
         </listitem>
 
         <listitem>
@@ -5104,13 +5109,13 @@
 
         <listitem>
           <para>
-            Inserting row: (1 x size of row)
+            Inserting row: (1 &times; size of row)
           </para>
         </listitem>
 
         <listitem>
           <para>
-            Inserting indexes: (1 x number of indexes)
+            Inserting indexes: (1 &times; number of indexes)
           </para>
         </listitem>
 
@@ -5147,7 +5152,7 @@
             rows at a time. This is considerably faster (many times
             faster in some cases) than using separate single-row
             <literal>INSERT</literal> statements. If you are adding data
-            to a non-empty table, you may tune the
+            to a non-empty table, you can tune the
             <literal>bulk_insert_buffer_size</literal> variable to make
             data insertion even faster. See
             <xref linkend="server-system-variables"/>.
@@ -5158,15 +5163,26 @@
           <para>
             If you are inserting a lot of rows from different clients,
             you can get higher speed by using the <literal>INSERT
-            DELAYED</literal> statement. See <xref linkend="insert"/>.
+            DELAYED</literal> statement. See
+            <xref linkend="insert-delayed"/>.
           </para>
         </listitem>
 
         <listitem>
           <para>
-            With <literal>MyISAM</literal> tables you can insert rows at
-            the same time that <literal>SELECT</literal> statements are
-            running if there are no deleted rows in the tables.
+            <indexterm>
+              <primary>concurrent inserts</primary>
+            </indexterm>
+
+            <indexterm>
+              <primary>inserts</primary>
+              <secondary>concurrent</secondary>
+            </indexterm>
+
+            For a <literal>MyISAM</literal> table, you can use
+            concurrent inserts to add rows at the same time that
+            <literal>SELECT</literal> statements are running if there
+            are no deleted rows in middle of the table.
           </para>
         </listitem>
 
@@ -5174,7 +5190,7 @@
           <para>
             When loading a table from a text file, use <literal>LOAD
             DATA INFILE</literal>. This is usually 20 times faster than
-            using a lot of <literal>INSERT</literal> statements. See
+            using <literal>INSERT</literal> statements. See
             <xref linkend="load-data"/>.
           </para>
         </listitem>
@@ -5182,8 +5198,9 @@
         <listitem>
           <para>
             With some extra work, it is possible to make <literal>LOAD
-            DATA INFILE</literal> run even faster when the table has
-            many indexes. Use the following procedure:
+            DATA INFILE</literal> run even faster for a
+            <literal>MyISAM</literal> table when the table has many
+            indexes. Use the following procedure:
           </para>
 
           <orderedlist>
@@ -5228,12 +5245,13 @@
 
             <listitem>
               <para>
-                Re-create the indexes with <command>myisamchk -r -q
+                Re-create the indexes with <command>myisamchk -rq
                 <replaceable>/path/to/db/tbl_name</replaceable></command>.
                 This creates the index tree in memory before writing it
-                to disk, which is much faster because it avoids lots of
-                disk seeks. The resulting index tree is also perfectly
-                balanced.
+                to disk, which is much faster that updating the index
+                during <literal>LOAD DATA INFILE</literal> because it
+                avoids lots of disk seeks. The resulting index tree is
+                also perfectly balanced.
               </para>
             </listitem>
 
@@ -5247,33 +5265,34 @@
           </orderedlist>
 
           <para>
-            Note that <literal>LOAD DATA INFILE</literal> also performs
-            the preceding optimization if you insert into an empty
-            <literal>MyISAM</literal> table; the main difference is that
-            you can let <command>myisamchk</command> allocate much more
-            temporary memory for the index creation than you might want
-            the server to allocate for index re-creation when it
-            executes the <literal>LOAD DATA INFILE</literal> statement.
+            Note that <literal>LOAD DATA INFILE</literal> performs the
+            preceding optimization automatically if the
+            <literal>MyISAM</literal> table into which you insert data
+            is empty. The main difference is that you can let
+            <command>myisamchk</command> allocate much more temporary
+            memory for the index creation than you might want the server
+            to allocate for index re-creation when it executes the
+            <literal>LOAD DATA INFILE</literal> statement.
           </para>
 
           <para>
-            You can also use <literal>ALTER TABLE
-            <replaceable>tbl_name</replaceable> DISABLE KEYS</literal>
-            instead of <command>myisamchk --keys-used=0 -rq
-            <replaceable>/path/to/db/tbl_name</replaceable></command>
-            and <literal>ALTER TABLE <replaceable>tbl_name</replaceable>
-            ENABLE KEYS</literal> instead of <command>myisamchk -r -q
-            <replaceable>/path/to/db/tbl_name</replaceable></command>.
-            In this way, you can also skip the <literal>FLUSH
-            TABLES</literal> steps.
+            You can also disable or enable the indexes for a
+            <literal>MyISAM</literal> table by using the following
+            statements rather than <command>myisamchk</command>. If you
+            use these statements, you can skip the <literal>FLUSH
+            TABLE</literal> operations:
           </para>
+
+<programlisting>
+ALTER TABLE <replaceable>tbl_name</replaceable> DISABLE KEYS;
+ALTER TABLE <replaceable>tbl_name</replaceable> ENABLE KEYS;
+</programlisting>
         </listitem>
 
         <listitem>
           <para>
-            You can speed up <literal>INSERT</literal> operations that
-            are performed with multiple statements by locking your
-            tables:
+            To speed up <literal>INSERT</literal> operations that are
+            performed with multiple statements, lock your tables:
           </para>
 
 <programlisting>
@@ -5287,16 +5306,16 @@
             This benefits performance because the index buffer is
             flushed to disk only once, after all
             <literal>INSERT</literal> statements have completed.
-            Normally there would be as many index buffer flushes as
+            Normally, there would be as many index buffer flushes as
             there are <literal>INSERT</literal> statements. Explicit
             locking statements are not needed if you can insert all rows
-            with a single statement.
+            with a single <literal>INSERT</literal>.
           </para>
 
           <para>
-            For transactional tables, you should use
-            <literal>BEGIN</literal> and <literal>COMMIT</literal>
-            instead of <literal>LOCK TABLES</literal> to obtain faster
+            For transactional tables, you should use <literal>START
+            TRANSACTION</literal> and <literal>COMMIT</literal> instead
+            of <literal>LOCK TABLES</literal> to obtain faster
             insertions.
           </para>
 
@@ -5307,12 +5326,28 @@
             example:
           </para>
 
-<programlisting>
-Connection 1 does 1000 inserts
-Connections 2, 3, and 4 do 1 insert
-Connection 5 does 1000 inserts
-</programlisting>
+          <orderedlist>
 
+            <listitem>
+              <para>
+                Connection 1 does 1000 inserts
+              </para>
+            </listitem>
+
+            <listitem>
+              <para>
+                Connections 2, 3, and 4 do 1 insert
+              </para>
+            </listitem>
+
+            <listitem>
+              <para>
+                Connection 5 does 1000 inserts
+              </para>
+            </listitem>
+
+          </orderedlist>
+
           <para>
             If you do not use locking, connections 2, 3, and 4 finish
             before 1 and 5. If you use locking, connections 2, 3, and 4
@@ -5342,8 +5377,8 @@
 
         <listitem>
           <para>
-            To get some more speed for <literal>MyISAM</literal> tables,
-            for both <literal>LOAD DATA INFILE</literal> and
+            To increase performance for <literal>MyISAM</literal>
+            tables, for both <literal>LOAD DATA INFILE</literal> and
             <literal>INSERT</literal>, enlarge the key cache by
             increasing the <literal>key_buffer_size</literal> system
             variable. See <xref linkend="server-parameters"/>.
@@ -5367,16 +5402,16 @@
       </para>
 
       <para>
-        Also, another way to get fast updates is to delay updates and
-        then do many updates in a row later. Performing multiple updates
+        Another way to get fast updates is to delay updates and then do
+        many updates in a row later. Performing multiple updates
         together is much quicker than doing one at a time if you lock
         the table.
       </para>
 
       <para>
-        Note that for a <literal>MyISAM</literal> table that uses
-        dynamic row format, updating a row to a longer total length may
-        split the row. If you do this often, it is very important to use
+        For a <literal>MyISAM</literal> table that uses dynamic row
+        format, updating a row to a longer total length may split the
+        row. If you do this often, it is very important to use
         <literal>OPTIMIZE TABLE</literal> occasionally. See
         <xref linkend="optimize-table"/>.
       </para>
@@ -5390,15 +5425,15 @@
       <para>
         The time required to delete individual rows is exactly
         proportional to the number of indexes. To delete rows more
-        quickly, you can increase the size of the key cache. See
-        <xref linkend="server-parameters"/>.
+        quickly, you can increase the size of the key cache by
+        increasing the <literal>key_buffer_size</literal> system
+        variable. See <xref linkend="server-parameters"/>.
       </para>
 
       <para>
-        If you want to delete all rows from a table, use
-        <literal>TRUNCATE TABLE
-        <replaceable>tbl_name</replaceable></literal> rather than
-        <literal>DELETE FROM
+        To delete all rows from a table, <literal>TRUNCATE TABLE
+        <replaceable>tbl_name</replaceable></literal> if faster than
+        than <literal>DELETE FROM
         <replaceable>tbl_name</replaceable></literal>. See
         <xref linkend="truncate"/>.
       </para>
@@ -5446,8 +5481,8 @@
         <listitem>
           <para>
             Always check whether all your queries really use the indexes
-            you have created in the tables. In MySQL, you can do this
-            with the <literal>EXPLAIN</literal> statement. See
+            that you have created in the tables. In MySQL, you can do
+            this with the <literal>EXPLAIN</literal> statement. See
             <xref linkend="explain"/>.
           </para>
         </listitem>
@@ -5464,19 +5499,20 @@
         <listitem>
           <para>
             With <literal>MyISAM</literal> tables that have no deleted
-            rows, you can insert rows at the end at the same time that
-            another query is reading from the table. If this is
-            important for you, you should consider using the table in
-            ways that avoid deleting rows. Another possibility is to run
-            <literal>OPTIMIZE TABLE</literal> after you have deleted a
-            lot of rows. See <xref linkend="myisam-storage-engine"/>.
+            rows in the middle, you can insert rows at the end at the
+            same time that another query is reading from the table. If
+            it is important to be able to do this, you should consider
+            using the table in ways that avoid deleting rows. Another
+            possibility is to run <literal>OPTIMIZE TABLE</literal> to
+            defragment the table after you have deleted a lot of rows
+            from it. See <xref linkend="myisam-storage-engine"/>.
           </para>
         </listitem>
 
         <listitem>
           <para>
             To fix any compression issues that may have occurred with
-            <literal>ARCHIVE</literal> tables you can always do an
+            <literal>ARCHIVE</literal> tables, you can use
             <literal>OPTIMIZE TABLE</literal>. See
             <xref linkend="archive-storage-engine"/>.
           </para>
@@ -5487,7 +5523,7 @@
             Use <literal>ALTER TABLE &hellip; ORDER BY
             <replaceable>expr1</replaceable>,
             <replaceable>expr2</replaceable>, &hellip;</literal> if you
-            mostly retrieve rows in
+            usually retrieve rows in
             <literal><replaceable>expr1</replaceable>,
             <replaceable>expr2</replaceable>, &hellip;</literal> order.
             By using this option after extensive changes to the table,
@@ -5500,8 +5536,8 @@
             In some cases, it may make sense to introduce a column that
             is <quote>hashed</quote> based on information from other
             columns. If this column is short and reasonably unique, it
-            may be much faster than a big index on many columns. In
-            MySQL, it is very easy to use this extra column:
+            may be much faster than a <quote>wide</quote> index on many
+            columns. In MySQL, it is very easy to use this extra column:
           </para>
 
 <programlisting>
@@ -5552,10 +5588,10 @@
 
           <para>
             This is very important when you use MySQL storage engines
-            such as <literal>MyISAM</literal>that has only table-level
+            such as <literal>MyISAM</literal> that has only table-level
             locking (multiple readers with single writers). This also
-            gives better performance with most databases, because the
-            row locking manager in this case has less to do.
+            gives better performance with most database systems, because
+            the row locking manager in this case has less to do.
           </para>
         </listitem>
 
@@ -5586,7 +5622,7 @@
             Take advantage of the fact that columns have default values.
             Insert values explicitly only when the value to be inserted
             differs from the default. This reduces the parsing that
-            MySQL needs to do and improves the insert speed.
+            MySQL must do and improves the insert speed.
           </para>
         </listitem>
 
@@ -5614,11 +5650,10 @@
 
         <listitem>
           <para>
-            Stored procedures or UDFs (user-defined functions) may be a
+            Stored routines or UDFs (user-defined functions) may be a
             good way to gain performance for some tasks. See
             <xref linkend="stored-procedures"/>, and
-            <xref linkend="adding-functions"/>, for more information
-            about these.
+            <xref linkend="adding-functions"/>, for more information.
           </para>
         </listitem>
 
@@ -5626,19 +5661,20 @@
           <para>
             You can always gain something by caching queries or answers
             in your application and then performing many inserts or
-            updates together. If your database supports table locks
-            (like 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"/>.
+            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"/>.
           </para>
         </listitem>
 
         <listitem>
           <para>
             Use <literal>INSERT DELAYED</literal> when you do not need
-            to know when your data is written. This speeds things up
-            because many rows can be written with a single disk write.
+            to know when your data is written. This reduces the overall
+            insertion impact because many rows can be written with a
+            single disk write.
           </para>
         </listitem>
 
@@ -5662,8 +5698,8 @@
         <listitem>
           <para>
             Use multiple-row <literal>INSERT</literal> statements to
-            store many rows with one SQL statement (many SQL servers
-            support this, including MySQL).
+            store many rows with one SQL statement. Many SQL servers
+            support this, including MySQL.
           </para>
         </listitem>
 
@@ -5685,8 +5721,8 @@
         <listitem>
           <para>
             Use <literal>OPTIMIZE TABLE</literal> once in a while to
-            avoid fragmentation with <literal>MyISAM</literal> tables
-            when using a dynamic table format. See
+            avoid fragmentation with dynamic-format
+            <literal>MyISAM</literal> tables. See
             <xref linkend="myisam-table-formats"/>.
           </para>
         </listitem>
@@ -5696,6 +5732,12 @@
             Use <literal>MEMORY</literal> (<literal>HEAP</literal>)
             tables when possible to get more speed. See
             <xref linkend="memory-storage-engine"/>.
+            <literal>MEMORY</literal> tables are useful for non-critical
+            data that is accessed often, such as information about the
+            last displayed banner for users who don't have cookies
+            enabled in their Web browser. User sessions are another
+            alternative available in many Web application environments
+            for handling volatile state data.
           </para>
         </listitem>
 
@@ -5711,22 +5753,14 @@
 
         <listitem>
           <para>
-            Use in-memory tables for non-critical data that is accessed
-            often, such as information about the last displayed banner
-            for users who don't have cookies enabled in their Web
-            browser. User sessions are another alternative available in
-            many Web application environments for handling volatile
-            state data.
+            Columns with identical information in different tables
+            should be declared to have identical data types so that
+            joins based on the corresponding columns will be faster.
           </para>
         </listitem>
 
         <listitem>
           <para>
-            Columns with identical information in different tables
-            should be declared to have identical data types.
-          </para>
-
-          <para>
             Try to keep column names simple. For example, in a table
             named <literal>customer</literal>, use a column name of
             <literal>name</literal> instead of
@@ -5772,7 +5806,7 @@
             database is likely to be stored in a more compact format
             than in the text file, so accessing it involves fewer disk
             accesses. You also save code in your application because you
-            do not have to parse your text files to find line and column
+            need not parse your text files to find line and column
             boundaries.
           </para>
         </listitem>
@@ -5794,7 +5828,7 @@
             index updates faster because they are not flushed to disk
             until the table is closed. The downside is that if something
             kills the server while such a table is open, you should
-            ensure that they are okay by running the server with the
+            ensure that the table is okay by running the server with the
             <option>--myisam-recover</option> option, or by running
             <command>myisamchk</command> before restarting the server.
             (However, even in this case, you should not lose anything by
@@ -5858,10 +5892,10 @@
       </indexterm>
 
       <para>
-        MySQL &current-series; supports table-level locking for
-        <literal>MyISAM</literal> and <literal>MEMORY</literal> tables,
-        page-level locking for <literal>BDB</literal> tables, and
-        row-level locking for <literal>InnoDB</literal> tables.
+        MySQL uses table-level locking for <literal>MyISAM</literal> and
+        <literal>MEMORY</literal> tables, page-level locking for
+        <literal>BDB</literal> tables, and row-level locking for
+        <literal>InnoDB</literal> tables.
       </para>
 
       <para>
@@ -5934,17 +5968,13 @@
 
       <para>
         When a lock is released, the lock is made available to the
-        threads in the write lock queue, and then to the threads in the
-        read lock queue.
+        threads in the write lock queue and then to the threads in the
+        read lock queue. This means that if you have many updates for a
+        table, <literal>SELECT</literal> statements wait until there are
+        no more updates.
       </para>
 
       <para>
-        This means that if you have many updates for a table,
-        <literal>SELECT</literal> statements wait until there are no
-        more updates.
-      </para>
-
-      <para>
         You can analyze the table lock contention on your system by
         checking the <literal>Table_locks_waited</literal> and
         <literal>Table_locks_immediate</literal> status variables:
@@ -5964,19 +5994,23 @@
         <primary>concurrent inserts</primary>
       </indexterm>
 
+      <indexterm>
+        <primary>inserts</primary>
+        <secondary>concurrent</secondary>
+      </indexterm>
+
       <para>
-        You can freely mix concurrent <literal>INSERT</literal> and
-        <literal>SELECT</literal> statements for a
-        <literal>MyISAM</literal> table without locks if the
-        <literal>INSERT</literal> statements are non-conflicting. That
-        is, you can insert rows into a <literal>MyISAM</literal> table
-        at the same time other clients are reading from it. No conflict
-        occurs if the data file contains no free blocks in the middle,
-        because in that case, rows always are inserted at the end of the
-        data file. (Holes can result from rows having been deleted from
-        or updated in the middle of the table.) If there are holes,
-        concurrent inserts are re-enabled automatically when all holes
-        have been filled with new data.
+        If a <literal>MyISAM</literal> table contains no free blocks in
+        the middle, rows always are inserted at the end of the data
+        file. In this case, you can freely mix concurrent
+        <literal>INSERT</literal> and <literal>SELECT</literal>
+        statements for a <literal>MyISAM</literal> table without locks.
+        That is, you can insert rows into a <literal>MyISAM</literal>
+        table at the same time other clients are reading from it. (Holes
+        can result from rows having been deleted from or updated in the
+        middle of the table. If there are holes, concurrent inserts are
+        disabled but are re-enabled automatically when all holes have
+        been filled with new data.)
       </para>
 
       <para>
@@ -7836,7 +7870,7 @@
           Instead, the server directly accesses the table indexes using
           native filesystem caching. Filesystem caching is not as
           efficient as using a key cache, so although queries execute, a
-          slowdown can be anticipated. Once the cache has been
+          slowdown can be anticipated. After the cache has been
           restructured, it becomes available again for caching indexes
           assigned to it, and the use of filesystem caching for the
           indexes ceases.

Modified: trunk/refman-5.1/optimization.xml
===================================================================
--- trunk/refman-5.1/optimization.xml	2006-01-16 21:11:46 UTC (rev 858)
+++ trunk/refman-5.1/optimization.xml	2006-01-16 21:42:59 UTC (rev 859)
@@ -1363,7 +1363,7 @@
 
               <para>
                 MySQL found no good index to use, but found that some of
-                indexes might be used once column values from preceding
+                indexes might be used after column values from preceding
                 tables are known. For each row combination in the
                 preceding tables, MySQL checks whether it is possible to
                 use a <literal>range</literal> or
@@ -4795,14 +4795,14 @@
         </para>
 
         <para>
-          For this method to work, it is sufficient that, for all
-          columns in a query referring to parts of the key coming before
-          or in between parts of the <literal>GROUP BY</literal> key,
-          there is a constant equality condition. The constants from the
-          equality conditions fill in any <quote>gaps</quote> in the
-          search keys so that it is possible to form complete prefixes
-          of the index. These index prefixes can be then used for index
-          lookups. If we require sorting of the <literal>GROUP
+          For this method to work, it is sufficient that there is a
+          constant equality condition for all columns in a query
+          referring to parts of the key coming before or in between
+          parts of the <literal>GROUP BY</literal> key. The constants
+          from the equality conditions fill in any <quote>gaps</quote>
+          in the search keys so that it is possible to form complete
+          prefixes of the index. These index prefixes then can be used
+          for index lookups. If we require sorting of the <literal>GROUP
           BY</literal> result, and it is possible to form search keys
           that are prefixes of the index, MySQL also avoids extra
           sorting operations because searching with prefixes in an
@@ -4822,7 +4822,7 @@
           <listitem>
             <para>
               There is a gap in the <literal>GROUP BY</literal>, but it
-              is covered by the condition <literal>c2 = 'a'</literal>.
+              is covered by the condition <literal>c2 = 'a'</literal>:
             </para>
 
 <programlisting>
@@ -4891,9 +4891,9 @@
             <literal>LIMIT</literal> clause must be selected, and most
             or all of them must be sorted, before it can be ascertained
             that the first <replaceable>row_count</replaceable> rows
-            have been found. In either case, once the rows have been
-            found, there is no need to sort any remainder of the result
-            set, and MySQL does not do so.
+            have been found. In either case, after the initial rows have
+            been found, there is no need to sort any remainder of the
+            result set, and MySQL does not do so.
           </para>
         </listitem>
 
@@ -4942,9 +4942,9 @@
         <listitem>
           <para>
             When the server uses temporary tables to resolve the query,
-            the <literal>LIMIT
-            <replaceable>row_count</replaceable></literal> clause is
-            used to calculate how much space is required.
+            it uses the <literal>LIMIT
+            <replaceable>row_count</replaceable></literal> clause to
+            calculate how much space is required.
           </para>
         </listitem>
 
@@ -4973,8 +4973,8 @@
         <listitem>
           <para>
             The table is so small that it is faster to perform a table
-            scan than a key lookup. This is common for tables with fewer
-            than 10 rows and a short row length.
+            scan than to bother with a key lookup. This is common for
+            tables with fewer than 10 rows and a short row length.
           </para>
         </listitem>
 
@@ -5000,7 +5000,7 @@
           <para>
             You are using a key with low cardinality (many rows match
             the key value) through another column. In this case, MySQL
-            assumes that by using the key it probably does a lot of key
+            assumes that by using the key it probably will do many key
             lookups and that a table scan would be faster.
           </para>
         </listitem>
@@ -5008,9 +5008,10 @@
       </itemizedlist>
 
       <para>
-        For small tables, a table scan often is appropriate. For large
-        tables, try the following techniques to avoid having the
-        optimizer incorrectly choose a table scan:
+        For small tables, a table scan often is appropriate and the
+        performance impact is negligible. For large tables, try the
+        following techniques to avoid having the optimizer incorrectly
+        choose a table scan:
       </para>
 
       <itemizedlist>
@@ -5028,13 +5029,17 @@
           <para>
             Use <literal>FORCE INDEX</literal> for the scanned table to
             tell MySQL that table scans are very expensive compared to
-            using the given index. See <xref linkend="select"/>.
+            using the given index:
           </para>
 
 <programlisting>
 SELECT * FROM t1, t2 FORCE INDEX (<replaceable>index_for_column</replaceable>)
   WHERE t1.<replaceable>col_name</replaceable>=t2.<replaceable>col_name</replaceable>;
 </programlisting>
+
+          <para>
+            See <xref linkend="select"/>.
+          </para>
         </listitem>
 
         <listitem>
@@ -5097,13 +5102,13 @@
 
         <listitem>
           <para>
-            Inserting row: (1 x size of row)
+            Inserting row: (1 &times; size of row)
           </para>
         </listitem>
 
         <listitem>
           <para>
-            Inserting indexes: (1 x number of indexes)
+            Inserting indexes: (1 &times; number of indexes)
           </para>
         </listitem>
 
@@ -5140,7 +5145,7 @@
             rows at a time. This is considerably faster (many times
             faster in some cases) than using separate single-row
             <literal>INSERT</literal> statements. If you are adding data
-            to a non-empty table, you may tune the
+            to a non-empty table, you can tune the
             <literal>bulk_insert_buffer_size</literal> variable to make
             data insertion even faster. See
             <xref linkend="server-system-variables"/>.
@@ -5151,15 +5156,26 @@
           <para>
             If you are inserting a lot of rows from different clients,
             you can get higher speed by using the <literal>INSERT
-            DELAYED</literal> statement. See <xref linkend="insert"/>.
+            DELAYED</literal> statement. See
+            <xref linkend="insert-delayed"/>.
           </para>
         </listitem>
 
         <listitem>
           <para>
-            With <literal>MyISAM</literal> tables you can insert rows at
-            the same time that <literal>SELECT</literal> statements are
-            running if there are no deleted rows in the tables.
+            <indexterm>
+              <primary>concurrent inserts</primary>
+            </indexterm>
+
+            <indexterm>
+              <primary>inserts</primary>
+              <secondary>concurrent</secondary>
+            </indexterm>
+
+            For a <literal>MyISAM</literal> table, you can use
+            concurrent inserts to add rows at the same time that
+            <literal>SELECT</literal> statements are running if there
+            are no deleted rows in middle of the table.
           </para>
         </listitem>
 
@@ -5167,7 +5183,7 @@
           <para>
             When loading a table from a text file, use <literal>LOAD
             DATA INFILE</literal>. This is usually 20 times faster than
-            using a lot of <literal>INSERT</literal> statements. See
+            using <literal>INSERT</literal> statements. See
             <xref linkend="load-data"/>.
           </para>
         </listitem>
@@ -5175,8 +5191,9 @@
         <listitem>
           <para>
             With some extra work, it is possible to make <literal>LOAD
-            DATA INFILE</literal> run even faster when the table has
-            many indexes. Use the following procedure:
+            DATA INFILE</literal> run even faster for a
+            <literal>MyISAM</literal> table when the table has many
+            indexes. Use the following procedure:
           </para>
 
           <orderedlist>
@@ -5221,12 +5238,13 @@
 
             <listitem>
               <para>
-                Re-create the indexes with <command>myisamchk -r -q
+                Re-create the indexes with <command>myisamchk -rq
                 <replaceable>/path/to/db/tbl_name</replaceable></command>.
                 This creates the index tree in memory before writing it
-                to disk, which is much faster because it avoids lots of
-                disk seeks. The resulting index tree is also perfectly
-                balanced.
+                to disk, which is much faster that updating the index
+                during <literal>LOAD DATA INFILE</literal> because it
+                avoids lots of disk seeks. The resulting index tree is
+                also perfectly balanced.
               </para>
             </listitem>
 
@@ -5240,33 +5258,34 @@
           </orderedlist>
 
           <para>
-            Note that <literal>LOAD DATA INFILE</literal> also performs
-            the preceding optimization if you insert into an empty
-            <literal>MyISAM</literal> table; the main difference is that
-            you can let <command>myisamchk</command> allocate much more
-            temporary memory for the index creation than you might want
-            the server to allocate for index re-creation when it
-            executes the <literal>LOAD DATA INFILE</literal> statement.
+            Note that <literal>LOAD DATA INFILE</literal> performs the
+            preceding optimization automatically if the
+            <literal>MyISAM</literal> table into which you insert data
+            is empty. The main difference is that you can let
+            <command>myisamchk</command> allocate much more temporary
+            memory for the index creation than you might want the server
+            to allocate for index re-creation when it executes the
+            <literal>LOAD DATA INFILE</literal> statement.
           </para>
 
           <para>
-            You can also use <literal>ALTER TABLE
-            <replaceable>tbl_name</replaceable> DISABLE KEYS</literal>
-            instead of <command>myisamchk --keys-used=0 -rq
-            <replaceable>/path/to/db/tbl_name</replaceable></command>
-            and <literal>ALTER TABLE <replaceable>tbl_name</replaceable>
-            ENABLE KEYS</literal> instead of <command>myisamchk -r -q
-            <replaceable>/path/to/db/tbl_name</replaceable></command>.
-            In this way, you can also skip the <literal>FLUSH
-            TABLES</literal> steps.
+            You can also disable or enable the indexes for a
+            <literal>MyISAM</literal> table by using the following
+            statements rather than <command>myisamchk</command>. If you
+            use these statements, you can skip the <literal>FLUSH
+            TABLE</literal> operations:
           </para>
+
+<programlisting>
+ALTER TABLE <replaceable>tbl_name</replaceable> DISABLE KEYS;
+ALTER TABLE <replaceable>tbl_name</replaceable> ENABLE KEYS;
+</programlisting>
         </listitem>
 
         <listitem>
           <para>
-            You can speed up <literal>INSERT</literal> operations that
-            are performed with multiple statements by locking your
-            tables:
+            To speed up <literal>INSERT</literal> operations that are
+            performed with multiple statements, lock your tables:
           </para>
 
 <programlisting>
@@ -5280,16 +5299,16 @@
             This benefits performance because the index buffer is
             flushed to disk only once, after all
             <literal>INSERT</literal> statements have completed.
-            Normally there would be as many index buffer flushes as
+            Normally, there would be as many index buffer flushes as
             there are <literal>INSERT</literal> statements. Explicit
             locking statements are not needed if you can insert all rows
-            with a single statement.
+            with a single <literal>INSERT</literal>.
           </para>
 
           <para>
-            For transactional tables, you should use
-            <literal>BEGIN</literal> and <literal>COMMIT</literal>
-            instead of <literal>LOCK TABLES</literal> to obtain faster
+            For transactional tables, you should use <literal>START
+            TRANSACTION</literal> and <literal>COMMIT</literal> instead
+            of <literal>LOCK TABLES</literal> to obtain faster
             insertions.
           </para>
 
@@ -5300,12 +5319,28 @@
             example:
           </para>
 
-<programlisting>
-Connection 1 does 1000 inserts
-Connections 2, 3, and 4 do 1 insert
-Connection 5 does 1000 inserts
-</programlisting>
+          <orderedlist>
 
+            <listitem>
+              <para>
+                Connection 1 does 1000 inserts
+              </para>
+            </listitem>
+
+            <listitem>
+              <para>
+                Connections 2, 3, and 4 do 1 insert
+              </para>
+            </listitem>
+
+            <listitem>
+              <para>
+                Connection 5 does 1000 inserts
+              </para>
+            </listitem>
+
+          </orderedlist>
+
           <para>
             If you do not use locking, connections 2, 3, and 4 finish
             before 1 and 5. If you use locking, connections 2, 3, and 4
@@ -5335,8 +5370,8 @@
 
         <listitem>
           <para>
-            To get some more speed for <literal>MyISAM</literal> tables,
-            for both <literal>LOAD DATA INFILE</literal> and
+            To increase performance for <literal>MyISAM</literal>
+            tables, for both <literal>LOAD DATA INFILE</literal> and
             <literal>INSERT</literal>, enlarge the key cache by
             increasing the <literal>key_buffer_size</literal> system
             variable. See <xref linkend="server-parameters"/>.
@@ -5360,16 +5395,16 @@
       </para>
 
       <para>
-        Also, another way to get fast updates is to delay updates and
-        then do many updates in a row later. Performing multiple updates
+        Another way to get fast updates is to delay updates and then do
+        many updates in a row later. Performing multiple updates
         together is much quicker than doing one at a time if you lock
         the table.
       </para>
 
       <para>
-        Note that for a <literal>MyISAM</literal> table that uses
-        dynamic row format, updating a row to a longer total length may
-        split the row. If you do this often, it is very important to use
+        For a <literal>MyISAM</literal> table that uses dynamic row
+        format, updating a row to a longer total length may split the
+        row. If you do this often, it is very important to use
         <literal>OPTIMIZE TABLE</literal> occasionally. See
         <xref linkend="optimize-table"/>.
       </para>
@@ -5383,15 +5418,15 @@
       <para>
         The time required to delete individual rows is exactly
         proportional to the number of indexes. To delete rows more
-        quickly, you can increase the size of the key cache. See
-        <xref linkend="server-parameters"/>.
+        quickly, you can increase the size of the key cache by
+        increasing the <literal>key_buffer_size</literal> system
+        variable. See <xref linkend="server-parameters"/>.
       </para>
 
       <para>
-        If you want to delete all rows from a table, use
-        <literal>TRUNCATE TABLE
-        <replaceable>tbl_name</replaceable></literal> rather than
-        <literal>DELETE FROM
+        To delete all rows from a table, <literal>TRUNCATE TABLE
+        <replaceable>tbl_name</replaceable></literal> if faster than
+        than <literal>DELETE FROM
         <replaceable>tbl_name</replaceable></literal>. See
         <xref linkend="truncate"/>.
       </para>
@@ -5439,8 +5474,8 @@
         <listitem>
           <para>
             Always check whether all your queries really use the indexes
-            you have created in the tables. In MySQL, you can do this
-            with the <literal>EXPLAIN</literal> statement. See
+            that you have created in the tables. In MySQL, you can do
+            this with the <literal>EXPLAIN</literal> statement. See
             <xref linkend="explain"/>.
           </para>
         </listitem>
@@ -5457,19 +5492,20 @@
         <listitem>
           <para>
             With <literal>MyISAM</literal> tables that have no deleted
-            rows, you can insert rows at the end at the same time that
-            another query is reading from the table. If this is
-            important for you, you should consider using the table in
-            ways that avoid deleting rows. Another possibility is to run
-            <literal>OPTIMIZE TABLE</literal> after you have deleted a
-            lot of rows. See <xref linkend="myisam-storage-engine"/>.
+            rows in the middle, you can insert rows at the end at the
+            same time that another query is reading from the table. If
+            it is important to be able to do this, you should consider
+            using the table in ways that avoid deleting rows. Another
+            possibility is to run <literal>OPTIMIZE TABLE</literal> to
+            defragment the table after you have deleted a lot of rows
+            from it. See <xref linkend="myisam-storage-engine"/>.
           </para>
         </listitem>
 
         <listitem>
           <para>
             To fix any compression issues that may have occurred with
-            <literal>ARCHIVE</literal> tables you can always do an
+            <literal>ARCHIVE</literal> tables, you can use
             <literal>OPTIMIZE TABLE</literal>. See
             <xref linkend="archive-storage-engine"/>.
           </para>
@@ -5480,7 +5516,7 @@
             Use <literal>ALTER TABLE &hellip; ORDER BY
             <replaceable>expr1</replaceable>,
             <replaceable>expr2</replaceable>, &hellip;</literal> if you
-            mostly retrieve rows in
+            usually retrieve rows in
             <literal><replaceable>expr1</replaceable>,
             <replaceable>expr2</replaceable>, &hellip;</literal> order.
             By using this option after extensive changes to the table,
@@ -5493,8 +5529,8 @@
             In some cases, it may make sense to introduce a column that
             is <quote>hashed</quote> based on information from other
             columns. If this column is short and reasonably unique, it
-            may be much faster than a big index on many columns. In
-            MySQL, it is very easy to use this extra column:
+            may be much faster than a <quote>wide</quote> index on many
+            columns. In MySQL, it is very easy to use this extra column:
           </para>
 
 <programlisting>
@@ -5545,10 +5581,10 @@
 
           <para>
             This is very important when you use MySQL storage engines
-            such as <literal>MyISAM</literal>that has only table-level
+            such as <literal>MyISAM</literal> that has only table-level
             locking (multiple readers with single writers). This also
-            gives better performance with most databases, because the
-            row locking manager in this case has less to do.
+            gives better performance with most database systems, because
+            the row locking manager in this case has less to do.
           </para>
         </listitem>
 
@@ -5579,7 +5615,7 @@
             Take advantage of the fact that columns have default values.
             Insert values explicitly only when the value to be inserted
             differs from the default. This reduces the parsing that
-            MySQL needs to do and improves the insert speed.
+            MySQL must do and improves the insert speed.
           </para>
         </listitem>
 
@@ -5607,11 +5643,10 @@
 
         <listitem>
           <para>
-            Stored procedures or UDFs (user-defined functions) may be a
+            Stored routines or UDFs (user-defined functions) may be a
             good way to gain performance for some tasks. See
             <xref linkend="stored-procedures"/>, and
-            <xref linkend="adding-functions"/>, for more information
-            about these.
+            <xref linkend="adding-functions"/>, for more information.
           </para>
         </listitem>
 
@@ -5619,19 +5654,20 @@
           <para>
             You can always gain something by caching queries or answers
             in your application and then performing many inserts or
-            updates together. If your database supports table locks
-            (like 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"/>.
+            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"/>.
           </para>
         </listitem>
 
         <listitem>
           <para>
             Use <literal>INSERT DELAYED</literal> when you do not need
-            to know when your data is written. This speeds things up
-            because many rows can be written with a single disk write.
+            to know when your data is written. This reduces the overall
+            insertion impact because many rows can be written with a
+            single disk write.
           </para>
         </listitem>
 
@@ -5655,8 +5691,8 @@
         <listitem>
           <para>
             Use multiple-row <literal>INSERT</literal> statements to
-            store many rows with one SQL statement (many SQL servers
-            support this, including MySQL).
+            store many rows with one SQL statement. Many SQL servers
+            support this, including MySQL.
           </para>
         </listitem>
 
@@ -5678,8 +5714,8 @@
         <listitem>
           <para>
             Use <literal>OPTIMIZE TABLE</literal> once in a while to
-            avoid fragmentation with <literal>MyISAM</literal> tables
-            when using a dynamic table format. See
+            avoid fragmentation with dynamic-format
+            <literal>MyISAM</literal> tables. See
             <xref linkend="myisam-table-formats"/>.
           </para>
         </listitem>
@@ -5688,6 +5724,12 @@
           <para>
             Use <literal>MEMORY</literal> tables when possible to get
             more speed. See <xref linkend="memory-storage-engine"/>.
+            <literal>MEMORY</literal> tables are useful for non-critical
+            data that is accessed often, such as information about the
+            last displayed banner for users who don't have cookies
+            enabled in their Web browser. User sessions are another
+            alternative available in many Web application environments
+            for handling volatile state data.
           </para>
         </listitem>
 
@@ -5703,22 +5745,14 @@
 
         <listitem>
           <para>
-            Use in-memory tables for non-critical data that is accessed
-            often, such as information about the last displayed banner
-            for users who don't have cookies enabled in their Web
-            browser. User sessions are another alternative available in
-            many Web application environments for handling volatile
-            state data.
+            Columns with identical information in different tables
+            should be declared to have identical data types so that
+            joins based on the corresponding columns will be faster.
           </para>
         </listitem>
 
         <listitem>
           <para>
-            Columns with identical information in different tables
-            should be declared to have identical data types.
-          </para>
-
-          <para>
             Try to keep column names simple. For example, in a table
             named <literal>customer</literal>, use a column name of
             <literal>name</literal> instead of
@@ -5764,7 +5798,7 @@
             database is likely to be stored in a more compact format
             than in the text file, so accessing it involves fewer disk
             accesses. You also save code in your application because you
-            do not have to parse your text files to find line and column
+            need not parse your text files to find line and column
             boundaries.
           </para>
         </listitem>
@@ -5786,7 +5820,7 @@
             index updates faster because they are not flushed to disk
             until the table is closed. The downside is that if something
             kills the server while such a table is open, you should
-            ensure that they are okay by running the server with the
+            ensure that the table is okay by running the server with the
             <option>--myisam-recover</option> option, or by running
             <command>myisamchk</command> before restarting the server.
             (However, even in this case, you should not lose anything by
@@ -5850,10 +5884,10 @@
       </indexterm>
 
       <para>
-        MySQL &current-series; supports table-level locking for
-        <literal>MyISAM</literal> and <literal>MEMORY</literal> tables,
-        page-level locking for <literal>BDB</literal> tables, and
-        row-level locking for <literal>InnoDB</literal> tables.
+        MySQL uses table-level locking for <literal>MyISAM</literal> and
+        <literal>MEMORY</literal> tables, page-level locking for
+        <literal>BDB</literal> tables, and row-level locking for
+        <literal>InnoDB</literal> tables.
       </para>
 
       <para>
@@ -5926,17 +5960,13 @@
 
       <para>
         When a lock is released, the lock is made available to the
-        threads in the write lock queue, and then to the threads in the
-        read lock queue.
+        threads in the write lock queue and then to the threads in the
+        read lock queue. This means that if you have many updates for a
+        table, <literal>SELECT</literal> statements wait until there are
+        no more updates.
       </para>
 
       <para>
-        This means that if you have many updates for a table,
-        <literal>SELECT</literal> statements wait until there are no
-        more updates.
-      </para>
-
-      <para>
         You can analyze the table lock contention on your system by
         checking the <literal>Table_locks_waited</literal> and
         <literal>Table_locks_immediate</literal> status variables:
@@ -5956,19 +5986,23 @@
         <primary>concurrent inserts</primary>
       </indexterm>
 
+      <indexterm>
+        <primary>inserts</primary>
+        <secondary>concurrent</secondary>
+      </indexterm>
+
       <para>
-        You can freely mix concurrent <literal>INSERT</literal> and
-        <literal>SELECT</literal> statements for a
-        <literal>MyISAM</literal> table without locks if the
-        <literal>INSERT</literal> statements are non-conflicting. That
-        is, you can insert rows into a <literal>MyISAM</literal> table
-        at the same time other clients are reading from it. No conflict
-        occurs if the data file contains no free blocks in the middle,
-        because in that case, rows always are inserted at the end of the
-        data file. (Holes can result from rows having been deleted from
-        or updated in the middle of the table.) If there are holes,
-        concurrent inserts are re-enabled automatically when all holes
-        have been filled with new data.
+        If a <literal>MyISAM</literal> table contains no free blocks in
+        the middle, rows always are inserted at the end of the data
+        file. In this case, you can freely mix concurrent
+        <literal>INSERT</literal> and <literal>SELECT</literal>
+        statements for a <literal>MyISAM</literal> table without locks.
+        That is, you can insert rows into a <literal>MyISAM</literal>
+        table at the same time other clients are reading from it. (Holes
+        can result from rows having been deleted from or updated in the
+        middle of the table. If there are holes, concurrent inserts are
+        disabled but are re-enabled automatically when all holes have
+        been filled with new data.)
       </para>
 
       <para>
@@ -7827,7 +7861,7 @@
           Instead, the server directly accesses the table indexes using
           native filesystem caching. Filesystem caching is not as
           efficient as using a key cache, so although queries execute, a
-          slowdown can be anticipated. Once the cache has been
+          slowdown can be anticipated. After the cache has been
           restructured, it becomes available again for caching indexes
           assigned to it, and the use of filesystem caching for the
           indexes ceases.

Thread
svn commit - mysqldoc@docsrva: r859 - in trunk: . refman-4.1 refman-5.0 refman-5.1paul16 Jan