bk commit - mysqldoc@docsrva tree (Mike.Hillyer:1.2801)

[mhillyer]

Below is the list of changes that have just been committed into a local
mysqldoc repository of root. When root does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://www.mysql.com/doc/I/n/Installing_source_tree.html

ChangeSet
  1.2801 05/06/17 15:07:21 Mike.Hillyer@stripped +1 -0
  Work in progress, need to be able to pull for changelog stuff.

  userguide/indexing.xml
    1.3 05/06/17 15:07:20 Mike.Hillyer@stripped +399 -9
    Work in progress, need to be able to pull for changelog stuff.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	Mike.Hillyer
# Host:	www.openwin.org
# Root:	/home/mysqldoc/mysqldoc

--- 1.2/userguide/indexing.xml	2005-06-15 16:05:48 -06:00
+++ 1.3/userguide/indexing.xml	2005-06-17 15:07:20 -06:00
@@ -225,18 +225,105 @@
 
    <title>Identifying Slow Queries with the Slow Query Log</title>
 
-   <para></para>
+   <para>
+    If you need to identify slow queries on a production MySQL server
+    you may benefit from using the MySQL Slow Query Log. When the MySQL
+    server is started with the <literal>--log-slow-queries</literal>
+    option, it writes all queries that take longer than a configurable
+    number of seconds to a log file. The queries in the Slow Query Log
+    can be further examined and optimized.
+   </para>
+
+   <para>
+    The Slow Query Log can also be activated by adding the
+    <literal>slog-slow-queries</literal> directive to the
+    <literal>[mysqld]</literal> section of your server configuration
+    file, or through the MySQL Administrator:
+   </para>
 
    <figure>
     <title>Activating the Slow Query Log using MySQL Administrator</title>
     <graphic fileref="images/indexing-choosing-slowlog-administrator-settings.png"
lang="en" format="PNG"/>
    </figure>
 
+   <para>
+    The <literal>Slow Queries Log</literal> option is found in the
+    <literal>Log Files</literal> tab of the <literal>Startup
+    Variables</literal> screen. Click the clipboard icon to the left of
+    the option to activate the Slow Query Log and click the
+    <guibutton>Apply Changes</guibutton> button. Once the Slow Query Log
+    is activated, restart the MySQL server using the <literal>Service
+    Control</literal> screen.
+   </para>
+
+   <para>
+    The default name of the log file is
+   
<filename><replaceable>server-name</replaceable>-slow.log</filename>.
+    If your server is named <literal>doomhammer.myserver.org</literal>,
+    the log file will be named
+    <filename>doomhammer.myserver.org-slow.log</filename>.
+   </para>
+
+   <para>
+    The Slow Query Log is a plain-text file that contains three lines
+    for every query logged:
+   </para>
+
+<programlisting>
+# User@Host: root[root] @ localhost [127.0.0.1]
+# Query_time: 0  Lock_time: 0  Rows_sent: 1  Rows_examined: 3
+SELECT last_name, first_name, actor_id FROM actor WHERE last_name = 'Brando';
+</programlisting>
+
+   <para>
+    The first line lists the username and hostname of the user who
+    executed the query. The second line lists the time taken to execute
+    the query, the time required to acquire the necessary locks, the
+    number of rows returned by the query, and the number of rows the
+    MySQL optimizer needed to examine. The final line of the entry shows
+    the query that was executed.
+   </para>
+
+   <para>
+    The Slow Query Log can also be read using the MySQL Administrator
+    using the <literal>Slow Log</literal> tab of the <literal>Server
+    Logs</literal> screen:
+   </para>
+
    <figure>
     <title>Viewing the Slow Query Log with MySQL Administrator</title>
     <graphic fileref="images/indexing-choosing-slowlog-administrator-view.png"
lang="en" format="PNG"/>
    </figure>
 
+   <para>
+    The Slow Queries Log determines whether a query is slow by how long
+    the query takes to execute in seconds, not counting the time
+    required to acquire table locks. The default time is two seconds and
+    can be adjusted by setting the <literal>long_query_time</literal>
+    option in the <literal>[mysqld]</literal> section of the server
+    configuration file. The <literal>long_query_time</literal> option
+    can also be set using the <literal>Log Files</literal> tab of the
+    <literal>Startup Variables</literal> screen of MySQL Administrator.
+   </para>
+
+   <para>
+    It should be noted that queries can appear in the Slow Query Log
+    even if they are properly optimized if the server load is high
+    enough to cause the query to take longer than the
+    <literal>long_query_time</literal>.
+   </para>
+
+   <para>
+    If you wish to log all queries that do not use indexes, regardless
+    of how long the queries take to execute, add the
+    <literal>log-queries-not-using-indexes</literal> option to the
+    <literal>[mysqld]</literal> section of your MySQL server
+    configuration file, or check the <literal>Log queries that don't use
+    indexes</literal> option of the <literal>Log Files</literal> tab of
+    the <literal>Startup Variables</literal> screen of MySQL
+    Administrator.
+   </para>
+
   </section>
 
 <!-- END SLOW LOG -->
@@ -251,7 +338,7 @@
 
   <para>
    Before creating a new index, it is important to know what indexes
-   currently exist for a given table. Index information can be retreived
+   currently exist for a given table. Index information can be retrieved
    using either the <literal>SHOW</literal> syntax or through use of the
    <literal>INFORMATION_SCHEMA</literal>. Index information can also be
    browsed using the MySQL GUI tools.
@@ -479,21 +566,314 @@
 
   <title>Creating Indexes</title>
 
-  <para></para>
+  <para>
+   Indexes can be created using either a <literal>CREATE
+   TABLE</literal>, <literal>CREATE INDEX</literal> or
<literal>ALTER
+   TABLE</literal> syntax. Before creating an index, you must know which
+   table the index will be added to, which column(s) the index will
+   apply to, the type of index you will create, and whether the index
+   will enforce uniqueness.
+  </para>
+
+  <section id="indexing-creating-create-table">
+
+   <title>Creating Indexes with the CREATE TABLE Statement</title>
+
+   <para>
+    Indexes can be created during table creation by specifying the index
+    information as part of the <literal>CREATE TABLE</literal> statement
+    using the following syntax:
+   </para>
+
+<programlisting>
+CREATE TABLE table_name(
+  column_name column_type [PRIMARY KEY|KEY|UNIQUE KEY],...
+  [PRIMARY KEY|INDEX|UNIQUE|FULLTEXT|SPATIAL] index_name (index_col_name,...)
+)
+</programlisting>
+
+   <para>
+    For example, this is a simplified version of the CREATE TABLE
+    statement for the <literal>inventory</literal> table:
+   </para>
+
+<programlisting>
+CREATE TABLE inventory (
+  inventory_id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
+  film_id INT UNSIGNED NOT NULL,
+  store_id INT UNSIGNED NOT NULL,
+  INDEX store_id_index (store_id)
+)
+</programlisting>
+
+   <para>
+    The primary key was created as part of the column creation line,
+    while the index on the <literal>store_id</literal> column was
+    created at the end of the <literal>CREATE TABLE</literal> statement.
+   </para>
+
+   <para>
+    It is necesscary to specify index information at the end of the
+    <literal>CREATE TABLE</literal> statement if the index contains more
+    that one column. For example, in the <literal>film_actor</literal>
+    table a single actor cannot appear more than once in the same film,
+    so the primary key is a combination of the
+    <literal>film_id</literal> and <literal>actor_id</literal>
columns:
+   </para>
+
+<programlisting>
+CREATE TABLE film_actor (
+  actor_id INT UNSIGNED NOT NULL,
+  film_id INT UNSIGNED NOT NULL,
+  PRIMARY KEY  (film_id, actor_id),
+)
+</programlisting>
+
+  </section>
+
+<!-- END CREATE TABLE -->
+
+  <section id="indexing-creating-create-index">
+
+   <title>Using the CREATE INDEX Syntax</title>
+
+   <para>
+    The <literal>CREATE INDEX</literal> statement has the following
+    syntax:
+   </para>
+
+<programlisting>
+  CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
+    ON table_name (index_col_name[(prefix length)],...)
+ </programlisting>
+
+   <para>
+    For example, to create an index on <literal>last_name</literal> and
+    <literal>first_name</literal> columns of the actor table, you would
+    execute the following statement:
+   </para>
+
+<programlisting>
+ CREATE INDEX last_first_name ON actor (last_name, first_name);
+</programlisting>
+
+   <para>
+    This example creates an index that enforces uniqueness on the
+    <literal>manager</literal> column of the
<literal>store</literal>,
+    ensuring no employee is so overworked that they have to manage two
+    locations:
+   </para>
+
+<programlisting>
+  CREATE UNIQUE INDEX unique_manager ON store (manager);
+ </programlisting>
+
+  </section>
+
+<!-- END CREATE INDEX -->
+
+  <section id="indexing-creating-alter-table">
+
+   <title>Using ALTER TABLE to Create Indexes</title>
+
+   <para>
+    You can use the <literal>ALTER TABLE</literal> statement to create
+    indexes with the following syntax:
+   </para>
+
+<programlisting>
+ALTER TABLE tbl_name ADD INDEX|UNIQUE|FULLTEXT|SPATIAL [index_name] (index_col_name,...)
+ALTER TABLE tbl_name ADD PRIMARY KEY (index_col_name,...)
+</programlisting>
+
+   <para>
+    For example, to create an index on <literal>last_name</literal> and
+    <literal>first_name</literal> columns of the actor table, you would
+    execute the following statement:
+   </para>
+
+<programlisting>
+ALTER TABLE actor ADD INDEX last_first_name (last_name, first_name);
+</programlisting>
+
+   <para>
+    This example creates an index that enforces uniqueness on the
+    <literal>manager</literal> column of the
<literal>store</literal>,
+    ensuring no employee is so overworked that they have to manage two
+    locations:
+   </para>
+
+<programlisting>
+ALTER TABLE actor ADD UNIQUE unique_manager (manager);
+</programlisting>
+
+   <para>
+    Here is an example of creating a primary key on the
+    <literal>staff</literal> table:
+   </para>
+
+<programlisting>
+ALTER TABLE staff ADD PRIMARY KEY (staff_id);
+</programlisting>
+
+   <para>
+    Primary keys can only be created with <literal>CREATE
+    TABLE</literal> and <literal>ALTER TABLE</literal> statements,
there
+    is no <literal>CREATE INDEX</literal> equivalent for primary keys.
+   </para>
+
+  </section>
+
+<!-- END ALTER TABLE -->
+
+  <section id="indexing-creating-prefix">
+
+   <title>Indexing the Prefix of a Column</title>
+
+   <para>
+    It is possible to index only a prefix of a
+    <literal>VARCHAR</literal>, <literal>CHAR</literal> or
+    <literal>TEXT</literal> column by placing the size of the prefix (in
+    characters) within brackets after the column name:
+   </para>
+
+<programlisting>
+ CREATE INDEX lname ON actor (last_name(5));
+</programlisting>
+
+   <para>
+    Indexing the prefix of a column decreases the size of the index
+    compared to indexing the entire column, which in turn increases the
+    performance of the index. An index can prefix up to 1000 bytes of a
+    column in <literal>MyISAM</literal> and
<literal>InnoDB</literal>,
+    and 255 bytes for all other storage engines.
+   </para>
+
+   <para>
+    One way to find the proper prefix size for a column is to perform
+    the following <literal>SELECT</literal> query:
+   </para>
+
+<programlisting>
+ SELECT COUNT(DISTINCT column_name) AS distinct_rows,
+ COUNT(DISTINCT(LEFT(column_name, <replaceable>N</replaceable>))) AS
prefix_distinct 
+ FROM table_name
+</programlisting>
+
+   <para>
+    Start with a <replaceable>N</replaceable> value of
+    <literal>3</literal> and increase the size of
+    <replaceable>N</replaceable> until the count of prefix_distinct
+    reaches a reasonable level, typically over half of the total number
+    of rows.
+   </para>
+
+  </section>
+
+<!-- END PREFIX -->
 
  </section>
 
 <!-- END CREATING -->
 
- <section id="indexing-fulltext">
+ <section id="indexing-composite">
 
-  <title>Using a FULLTEXT Index</title>
+  <title>Creating and Using Composite Indexes</title>
 
-  <para></para>
+  <para>
+   When executing a <literal>SELECT</literal> query, MySQL only uses one
+   index per table involved in the query. If the
+   <literal>WHERE</literal> clause of the query references more than one
+   column, a single-column index may be less than optimal. For example,
+   say you were executing the following query:
+  </para>
+
+<programlisting>
+SELECT actor_id 
+FROM actor 
+WHERE last_name = 'Johnson'
+  AND first_name = 'Robert'
+</programlisting>
+
+  <para>
+   If the table had an index on the <literal>last_name</literal> column,
+   the index could be used to narrow the table down to all actors with
+   the last name <literal>Johnson</literal>, but MySQL would still have
+   to scan all the matched rows to find actors with the first name
+   <literal>Robert</literal>.
+  </para>
+
+  <para>
+   By using a composite index, or an index on multiple columns, the
+   preceding query could be fully optimized. Here is an example of a
+   composite index on the <literal>actor</literal> table:
+  </para>
+
+<programlisting>
+CREATE INDEX last_first_name ON actor (last_name, first_name);
+</programlisting>
+
+  <para>
+   With such a composite index, MySQL can first find the last name
+   <literal>Johnson</literal> in the table, then search for the first
+   name <literal>Robert</literal> in the matching index entries.
+  </para>
+
+  <para>
+   Composite indexes can also be partially used when the columns in the
+   <literal>WHERE</literal> clause of a query appear in the left-most
+   part of the composite index. For instance, the following query would
+   make use of the composite index we have created:
+  </para>
+
+<programlisting>
+SELECT first_name 
+FROM actor 
+WHERE last_name = 'Johnson'
+</programlisting>
+
+  <para>
+   However, the following query would not make use of our composite
+   index:
+  </para>
+
+<programlisting>
+SELECT last_name 
+FROM actor 
+WHERE first_name = 'Robert'
+</programlisting>
+
+  <para>
+   The second example does not make use of the composite index because
+   the <literal>first_name</literal> column is not the left-most part of
+   the index columns.
+  </para>
+
+  <para>
+   This rule applies no matter how many parts a composite index has; if
+   you have an index on (<literal>columnA</literal>,
+   <literal>columnB</literal>, <literal>columnC</literal>,
+   <literal>columnD</literal>), the index will be used on queries that
+   contain the following columns in the <literal>WHERE</literal> clause:
+   (<literal>columnA</literal>), (<literal>columnA</literal>,
+   <literal>columnB</literal>),(<literal>columnA</literal>,
+   <literal>columnB</literal>, <literal>columnC</literal>), and
+   (<literal>columnA</literal>, <literal>columnB</literal>,
+   <literal>columnC</literal>, <literal>columnD</literal>). You
would
+   not be able to create any queries without <literal>columnA</literal>
+   and expect the composite index to be used.
+  </para>
+
+  <para>
+   For a more detailed description on when an index will be used, see
+   the section titled
+   <ulink url="http://dev.mysql.com/doc/mysql/en/mysql-indexes.html">How
+   MySQL Uses Indexes</ulink> in the MySQL Reference Manual.
+  </para>
 
  </section>
 
-<!-- END FULLTEXT -->
+<!-- END COMPOSITE -->
 
  <section id="indexing-dropping">
 
@@ -505,9 +885,19 @@
 
 <!-- END DROPPING -->
 
- <section id="indexing-optimizing">
+ <section id="indexing-fulltext">
+
+  <title>Using FULLTEXT Indexes</title>
+
+  <para></para>
+
+ </section>
+
+<!-- END FULLTEXT -->
+
+ <section id="indexing-explain">
 
-  <title>Using EXPLAIN to Improve Indexing</title>
+  <title>Using EXPLAIN to Optimize Indexing</title>
 
   <para></para>