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.3070 05/07/20 09:51:24 Mike.Hillyer@stripped +7 -0
Document bugfixed.
userguide/installing.xml
1.2 05/07/20 09:51:23 Mike.Hillyer@stripped +17 -2
Work in progress.
userguide/indexing.xml
1.10 05/07/20 09:51:23 Mike.Hillyer@stripped +809 -742
Added some reviewer edits.
refman/triggers.xml
1.9 05/07/20 09:51:23 Mike.Hillyer@stripped +16 -4
Document new drop syntax.
refman/news.xml
1.81 05/07/20 09:51:23 Mike.Hillyer@stripped +108 -16
Bugfix Documentation:
5906
7249
11057
11650
11822
12001
8758
5892
6182
8751
refman-5.0/triggers.xml
1.9 05/07/20 09:51:23 Mike.Hillyer@stripped +16 -4
Sync
refman-5.0/news.xml
1.67 05/07/20 09:51:23 Mike.Hillyer@stripped +77 -4
Sync
refman-4.1/news.xml
1.58 05/07/20 09:51:22 Mike.Hillyer@stripped +22 -7
Sync
# 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.9/userguide/indexing.xml 2005-06-30 17:05:42 -06:00
+++ 1.10/userguide/indexing.xml 2005-07-20 09:51:23 -06:00
@@ -3,278 +3,332 @@
"http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd">
<chapter id="indexing">
- <title>Indexing Data</title>
+ <title>Indexing Data</title>
- <section id="indexing-introduction">
+ <section id="indexing-introduction">
- <title>Introduction to Indexing</title>
+ <title>Introduction to Indexing</title>
- <para>
- <emphasis>Note: Experienced users may wish to skip this
- section.</emphasis>
- </para>
+ <para>
+ <emphasis>Note: Experienced users may wish to skip this
+ section.</emphasis>
+ </para>
+
+ <para>
+ An index in a relational database serves much the same purpose as
+ an index in a book: books are not (typically) organized
+ alphabetically by subject, but are instead organized into logical
+ chapters and parts. This works well until you need to find all
+ references to a specific subject.
+ </para>
+
+ <para>
+ When you find yourself in such a situation you look at the index.
+ In the index, the contents of the book are listed in alphabetical
+ order, with a page number displayed for each subject.
+ </para>
+
+ <para>
+ Rather than go through the book page by page, you can look a
+ subject up in the index, then move to the page number in question
+ and scan the page for the subject you are interested in.
+ </para>
+
+ <para>
+ In a relational database, rows are not stored in a table in any
+ particular order. An index creates a sorted version of a column
+ (or set of columns) from a table that can be searched more
+ efficiently than the table itself.
+ </para>
+
+ <para>
+ Each entry in the index stores either the row itself (in the case
+ of InnoDB), or a pointer to the row's location in the data file
+ (similar to a page number in a book index).
+ </para>
+
+ <para>
+ Effective indexing can greatly improve the performance of
+ <literal>SELECT</literal> queries. When a specific column value is
+ searched for, and an appropriate index is <emphasis>not</emphasis>
+ available, the MySQL server must read every row of the table
+ looking for matching rows. When an appropriate index is available,
+ the MySQL server can look in the index for the appropriate values.
+ If you are querying only columns that appear in the index, the
+ MySQL server skips reading the table itself and return data
+ directly from the index.
+ </para>
+
+ <para>
+ This chapter covers the following topics:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Types of Indexes
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ INDEX
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ PRIMARY KEY
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ UNIQUE INDEX
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ SPATIAL INDEX
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ FULLTEXT INDEX
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </listitem>
+
+ <listitem>
+ <para>
+ Choosing Columns to Index
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Displaying Index Information
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Using <literal>SHOW</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Using the <literal>INFORMATION_SCHEMA</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Using MySQL Administrator
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </listitem>
+
+ <listitem>
+ <para>
+ Creating Indexes
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ Using <literal>CREATE TABLE</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Using <literal>CREATE INDEX</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Using <literal>ALTER TABLE</literal>
+ </para>
+ </listitem>
+
+ </itemizedlist>
+ </listitem>
+
+ <listitem>
+ <para>
+ Composite Indexes
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Dropping Indexes
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Using <literal>FULLTEXT</literal>
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Optimizing Indexes
+ </para>
+ </listitem>
- <para>
- An index in a relational database serves much the same purpose as a
- index in a book: books are not (typically) organized alphabetically
- by subject, but are instead organized into logical chapters and
- parts. This works well until you need to find all references to a
- specific subject (such as indexing).
- </para>
+ </itemizedlist>
- <para>
- When you find yourself in such a situation you look at the index. In
- the index, the contents of the book are listed in alphabetical order,
- with a page number displayed for each subject.
- </para>
-
- <para>
- Rather than go through the book page by page, you can look a subject
- up in the index, then move to the page number in question and scan
- the page for the subject you are interested in.
- </para>
-
- <para>
- In a relational database, rows are not stored in a table in any
- particular order. An index creates a sorted version of a column or
- set of columns from a table that can be searched more efficiently.
- </para>
-
- <para>
- Each entry in the index stores either the row itself (in the case of
- InnoDB), or a pointer to the row's location in the data file (similar
- to a page number in a book index).
- </para>
-
- <para>
- Effective indexing can greatly improve the performance of
- <literal>SELECT</literal> queries. When a specific column value is
- searched for, and an appropriate index is not available, the MySQL
- server must read every row of the table looking for matching rows.
- When an appropriate index is available, the MySQL server can look in
- the index for the appropriate values. If you are querying only
- columns that appear in the index, the MySQL server can skip reading
- the table itself and return data directly from the index.
- </para>
-
- <para>
- This chapter covers the following topics:
- </para>
-
- <itemizedlist>
-
- <listitem><para>
- Types of Indexes
- </para>
-
- <itemizedlist>
-
- <listitem><para>
- INDEX
- </para></listitem>
-
- <listitem><para>
- PRIMARY KEY
- </para></listitem>
-
- <listitem><para>
- UNIQUE INDEX
- </para></listitem>
-
- <listitem><para>
- SPATIAL INDEX
- </para></listitem>
-
- <listitem><para>
- FULLTEXT INDEX
- </para></listitem>
-
- </itemizedlist></listitem>
-
- <listitem><para>
- Choosing Columns to Index
- </para></listitem>
-
- <listitem><para>
- Displaying Index Information
- </para>
-
- <itemizedlist>
-
- <listitem><para>
- Using <literal>SHOW</literal>
- </para></listitem>
-
- <listitem><para>
- Using the <literal>INFORMATION_SCHEMA</literal>
- </para></listitem>
-
- <listitem><para>
- Using MySQL Administrator
- </para></listitem>
-
- </itemizedlist></listitem>
-
- <listitem><para>
- Creating Indexes
- </para>
-
- <itemizedlist>
-
- <listitem><para>
- Using <literal>CREATE TABLE</literal>
- </para></listitem>
-
- <listitem><para>
- Using <literal>CREATE INDEX</literal>
- </para></listitem>
-
- <listitem><para>
- Using <literal>ALTER TABLE</literal>
- </para></listitem>
-
- </itemizedlist></listitem>
-
- <listitem><para>
- Composite Indexes
- </para></listitem>
-
- <listitem><para>
- Dropping Indexes
- </para></listitem>
-
- <listitem><para>
- Using <literal>FULLTEXT</literal>
- </para></listitem>
-
- <listitem><para>
- Optimizing Indexes
- </para></listitem>
-
- </itemizedlist>
-
- </section>
+ </section>
<!-- END INTRODUCTION -->
- <section id="indexing-types">
+ <section id="indexing-types">
- <title>Types of Indexes</title>
+ <title>Types of Indexes</title>
- <para>
- There are five different index types available for use in MySQL:
- </para>
-
- <itemizedlist>
-
- <listitem><para>
- <literal>INDEX</literal>: The standard table index. This type of
- index speeds up queries, but does not enforce uniqueness or uniquely
- identify the row.
- </para></listitem>
-
- <listitem><para>
- <literal>PRIMARY</literal>: This index is placed on the
- <literal>PRIMARY KEY</literal> of the table. Each entry in the index
- must be unique and the column(s) of this index are considered to be
- the unique identifier of the row within the table.
- </para></listitem>
-
- <listitem><para>
- <literal>UNIQUE</literal>: This index enforces that the column(s)
- being indexed are unique within the table, but is not considered to
- be the unique row identifier for the table.
- </para></listitem>
-
- <listitem><para>
- <literal>FULLTEXT</literal>: This index is used to increase the
- efficiency of querying natural language information in
- <literal>CHAR</literal>, <literal>VARCHAR</literal>, and
- <literal>TEXT</literal> columns. The <literal>FULLTEXT</literal>
- index is available for the
- <link linkend="storage-engines-myisam"><literal>MyISAM</literal>
- storage engine</link> only.
- </para></listitem>
-
- <listitem><para>
- <literal>SPATIAL</literal>: This index is used with
- <literal>GIS</literal> applications to improve the performance of
- functions such as <literal>MBRContains()</literal> or
- <literal>MBRWithin()</literal>. The <literal>SPATIAL</literal> index
- is available for the <literal>MyISAM</literal> storage engine only.
- </para></listitem>
-
- </itemizedlist>
-
- <para>
- Your choice of index usually depends on whether a given column should
- contain unique values: if a column contains unique values and those
- values are considered to uniquely identify a row (such as
- <literal>ISBN</literal>, <literal>UPC</literal>, or
- <literal>SSN</literal> values), choose a <literal>PRIMARY</literal>
- index. If your values are unique, but are not considered to uniquely
- identify the row, choose a <literal>UNIQUE</literal> index. If you
- wish to improve the performance of queries that match values in a
- column, use a regular <literal>INDEX</literal>. Only one
- <literal>PRIMARY</literal> index is allowed per table, all other
- index types can occur multiple times in a single table.
- </para>
-
- <para>
- In special cases you may need to use <literal>FULLTEXT</literal> and
- <literal>SPATIAL</literal> indexes. If you are searching columns that
- contain natural language, you should use a
- <literal>FULLTEXT</literal> index. An example of natural language
- would be the titles and bodies of a collection of articles, as
- opposed to textual data such as person or place names that should be
- indexed using a regular <literal>INDEX</literal>. The
- <literal>SPATIAL</literal> index is for use with
- <literal>GIS</literal> data only.
- </para>
+ <para>
+ There are five different index types available for use in MySQL:
+ </para>
+
+ <itemizedlist>
+
+ <listitem>
+ <para>
+ <literal>INDEX</literal>: The standard table index. This type
+ of index speeds up queries, but does not enforce uniqueness or
+ uniquely identify the row.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>PRIMARY</literal>: This index is placed on the
+ <literal>PRIMARY KEY</literal> of the table. Each entry in the
+ index must be unique and the column(s) of this index are
+ considered to be the unique identifier of the row within the
+ table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>UNIQUE</literal>: This index enforces that the
+ column(s) being indexed are unique within the table, but is
+ not considered to be the unique row identifier for the table.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>FULLTEXT</literal>: This index is used to increase
+ the efficiency of querying natural language information in
+ <literal>CHAR</literal>, <literal>VARCHAR</literal>, and
+ <literal>TEXT</literal> columns. The
+ <literal>FULLTEXT</literal> index is available for the
+ <link linkend="storage-engines-myisam"><literal>MyISAM</literal>
+ storage engine</link> only.
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>SPATIAL</literal>: This index is used with
+ <literal>GIS</literal> applications to improve the performance
+ of functions such as <literal>MBRContains()</literal> or
+ <literal>MBRWithin()</literal>. The <literal>SPATIAL</literal>
+ index is available for the <literal>MyISAM</literal> storage
+ engine only.
+ </para>
+ </listitem>
+
+ </itemizedlist>
+
+ <para>
+ Your choice of index usually depends on whether a given column
+ should contain unique values: if a column contains unique values
+ and those values are considered to uniquely identify a row (such
+ as <literal>ISBN</literal>, <literal>UPC</literal>, or
+ <literal>SSN</literal> values), choose a
+ <literal>PRIMARY</literal> index. If your values are unique, but
+ are not considered to uniquely identify the row, choose a
+ <literal>UNIQUE</literal> index. If you wish to improve the
+ performance of queries that match values in a column, use a
+ regular <literal>INDEX</literal>. Only one
+ <literal>PRIMARY</literal> index is allowed per table, all other
+ index types can occur multiple times in a single table.
+ </para>
+
+ <para>
+ In special cases you may need to use <literal>FULLTEXT</literal>
+ and <literal>SPATIAL</literal> indexes. If you are searching
+ columns that contain natural language, you should use a
+ <literal>FULLTEXT</literal> index. An example of natural language
+ would be the titles and bodies of a collection of articles, as
+ opposed to simple textual data such as person or place names,
+ which should be indexed using a regular <literal>INDEX</literal>.
+ The <literal>SPATIAL</literal> index is for use with
+ <literal>GIS</literal> data only.
+ </para>
- </section>
+ </section>
<!-- END TYPES -->
- <section id="indexing-choosing">
+ <section id="indexing-choosing">
- <title>Choosing Columns to Index</title>
+ <title>Choosing Columns to Index</title>
- <para>
- To use indexes effectively, it is necessary to identify those queries
- that are executing slowly and not using indexes, or columns which
- contain unique values. Slow queries can be identified by directly
- reviewing the queries you use or by using the MySQL Slow Query Log.
- </para>
-
- <section id="indexing-choosing-reviewing">
-
- <title>Reviewing Queries for Index Usage</title>
-
- <para>
- To identify queries that make good candidates for indexing, look at
- the <literal>WHERE</literal>, <literal>GROUP BY</literal>, and
- <literal>ORDER BY</literal> clauses of your queries.
- </para>
-
- <para>
- For example, look at the following query performed against the
- <literal>sakila</literal> sample database:
- </para>
+ <para>
+ To use indexes effectively, it is necessary to identify those
+ queries that are executing slowly and are not using indexes, or
+ columns which contain unique values. Slow queries can be
+ identified by directly reviewing the queries you use or by using
+ the MySQL Slow Query Log.
+ </para>
+
+ <section id="indexing-choosing-reviewing">
+
+ <title>Reviewing Queries for Index Usage</title>
+
+ <para>
+ To identify queries that make good candidates for indexing, look
+ at the <literal>WHERE</literal>, <literal>GROUP BY</literal>,
+ and <literal>ORDER BY</literal> clauses of your queries.
+ </para>
+
+ <para>
+ For example, look at the following query performed against the
+ <literal>sakila</literal> sample database:
+ </para>
<programlisting>
<userinput>SELECT last_name, first_name FROM actor WHERE last_name = 'Walken'</userinput>
</programlisting>
- <para>
- In this case the WHERE clause contains a reference to the
- <literal>last_name</literal> column of the <literal>actor</literal>
- table.
- </para>
-
- <para>
- Here is another example:
- </para>
+ <para>
+ In this case the WHERE clause contains a reference to the
+ <literal>last_name</literal> column of the
+ <literal>actor</literal> table.
+ </para>
+
+ <para>
+ Here is another example:
+ </para>
<programlisting>
<userinput>
@@ -286,82 +340,83 @@
</userinput>
</programlisting>
- <para>
- In this case the <literal>film_id</literal> and
- <literal>store_id</literal> columns are candidates for indexing.
- </para>
-
- <para>
- Once you have identified candidate columns, you need to evaluate how
- often the column is involved in a query: the most often a column is
- referenced in your various queries, the stronger a candidate it
- becomes for indexing.
- </para>
-
- <para>
- Each index you add to a table will have a negative effect on the
- performance of <literal>INSERT</literal>, <literal>UPDATE</literal>
- and <literal>DELETE</literal> queries because not only does the row
- data need to be changed, the index information must also be updated
- for each affected index. Over-indexing can lead to performance loss.
- </para>
-
- <para>
- Once you have identified your candidate columns you can check
- whether they are already indexed by displaying the existing indexes
- on a table.
- </para>
+ <para>
+ In this case the <literal>film_id</literal> and
+ <literal>store_id</literal> columns are candidates for indexing.
+ </para>
+
+ <para>
+ Once you have identified candidate columns, you need to evaluate
+ how often the column is involved in a query: the most often a
+ column is referenced in your various queries, the stronger a
+ candidate it becomes for indexing.
+ </para>
+
+ <para>
+ Each index you add to a table will have a negative effect on the
+ performance of <literal>INSERT</literal>,
+ <literal>UPDATE</literal> and <literal>DELETE</literal> queries
+ because not only does the row data need to be changed, the index
+ information must also be updated for each affected index.
+ Over-indexing can lead to performance loss.
+ </para>
+
+ <para>
+ Once you have identified your candidate columns you can check
+ whether they are already indexed by displaying the existing
+ indexes on a table.
+ </para>
- </section>
+ </section>
<!-- END REVIEWING -->
- <section id="indexing-choosing-slowlog">
+ <section id="indexing-choosing-slowlog">
- <title>Identifying Slow Queries with the Slow Query Log</title>
+ <title>Identifying Slow Queries with the Slow Query Log</title>
- <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 Slow Query Log enabled, 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. For more information see
- <xref linkend="logfiles-slow-query-log" />
- </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 Slow Query Log enabled, 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. For more information see
+ <xref linkend="logfiles-slow-query-log" />
+ </para>
- </section>
+ </section>
<!-- END SLOW LOG -->
- </section>
+ </section>
<!-- END CHOOSING -->
- <section id="indexing-displaying">
+ <section id="indexing-displaying">
- <title>Displaying Table Indexes</title>
+ <title>Displaying Table Indexes</title>
- <para>
- Before creating a new index, it is important to know what indexes
- 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>.
- <literal>INFORMATION_SCHEMA</literal> is a standard method for
- accessing information, while <literal>SHOW</literal> is an extension
- of the SQL standard. Index information can also be browsed using the
- MySQL GUI tools.
- </para>
-
- <section id="indexing-displaying-show">
-
- <title>Displaying Table Indexes Using the SHOW Command</title>
-
- <para>
- The <literal>SHOW</literal> command can be used within the
- <link linkend="glossary-active-schema">active schema</link> to
- display the index information for a table:
- </para>
+ <para>
+ Before creating a new index, it is important to know what indexes
+ 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>.
+ <literal>INFORMATION_SCHEMA</literal> is a standard method for
+ accessing information, while <literal>SHOW</literal> is an
+ extension of the SQL standard. Index information can also be
+ browsed using the MySQL GUI tools.
+ </para>
+
+ <section id="indexing-displaying-show">
+
+ <title>Displaying Table Indexes Using the SHOW Command</title>
+
+ <para>
+ The <literal>SHOW</literal> command can be used within the
+ <link linkend="glossary-active-schema">active schema</link> to
+ display the index information for a table:
+ </para>
<programlisting>
mysql> <userinput>USE sakila;</userinput>
@@ -410,54 +465,56 @@
3 rows in set (0.00 sec)
</programlisting>
- <remark>
- [MH] UPDATEME WHEN SAMPLE DB IS FINISHED
- </remark>
-
- <para>
- The output of the <literal>SHOW</literal> command shows that there
- are two indexes on the <literal>film</literal> table:
- <literal>PRIMARY</literal> and
- <literal>Title_Description_Fulltext</literal> (as seen from the
- <literal>Key_name</literal> value).
- </para>
-
- <para>
- The columns being indexed are listed in the
- <literal>Column_name</literal> field. In this case there are indexes
- on the <literal>film_id</literal>, <literal>title</literal>, and
- <literal>description</literal> columns.
- </para>
-
- <para>
- The <literal>title</literal> and <literal>description</literal>
- columns form two parts of the
- <literal>Title_Description_Fulltext</literal> index, with the
- <literal>title</literal> column appearing before the
- <literal>description</literal> column in the index, according to the
- <literal>Seq_in_index</literal> field.
- </para>
-
- <para>
- You can determine whether or not an index enforces uniqueness by the
- <literal>Non_unique</literal> field: <literal>0</literal> indicates
- that the index enforces uniqueness, <literal>1</literal> indicates
- that the index does not enforce uniqueness.
- </para>
+ <remark>
+ [MH] UPDATEME WHEN SAMPLE DB IS FINISHED
+ </remark>
+
+ <para>
+ The output of the <literal>SHOW</literal> command shows that
+ there are two indexes on the <literal>film</literal> table:
+ <literal>PRIMARY</literal> and
+ <literal>Title_Description_Fulltext</literal> (as seen from the
+ <literal>Key_name</literal> value).
+ </para>
+
+ <para>
+ The columns being indexed are listed in the
+ <literal>Column_name</literal> field. In this case there are
+ indexes on the <literal>film_id</literal>,
+ <literal>title</literal>, and <literal>description</literal>
+ columns.
+ </para>
+
+ <para>
+ The <literal>title</literal> and <literal>description</literal>
+ columns form two parts of the
+ <literal>Title_Description_Fulltext</literal> index, with the
+ <literal>title</literal> column appearing before the
+ <literal>description</literal> column in the index, according to
+ the <literal>Seq_in_index</literal> field.
+ </para>
+
+ <para>
+ You can determine whether or not an index enforces uniqueness by
+ the <literal>Non_unique</literal> field: <literal>0</literal>
+ indicates that the index enforces uniqueness,
+ <literal>1</literal> indicates that the index does not enforce
+ uniqueness.
+ </para>
- </section>
+ </section>
<!-- END SHOW -->
- <section id="indexing-displaying-information-schema">
+ <section id="indexing-displaying-information-schema">
- <title>Displaying Table Indexes Using the INFORMATION_SCHEMA</title>
+ <title>Displaying Table Indexes Using the INFORMATION_SCHEMA</title>
- <para>
- As an alternative to the <literal>SHOW</literal> command, users can
- query the <literal>STATISTICS</literal> table of the
- <literal>INFORMATION_SCHEMA</literal>.
- </para>
+ <para>
+ As an alternative to the <literal>SHOW</literal> command, users
+ can query the <literal>STATISTICS</literal> table of the
+ <literal>INFORMATION_SCHEMA</literal>.
+ </para>
<programlisting>
mysql> <userinput>SELECT * FROM INFORMATION_SCHEMA.STATISTICS</userinput>
@@ -514,101 +571,102 @@
3 rows in set (0.00 sec)
</programlisting>
- <remark>
- [MH] UPDATEME WHEN SAMPLE DB IS FINISHED
- </remark>
-
- <para>
- The output of a query to the <literal>INFORMATION_SCHEMA</literal>
- closely matches that of the <literal>SHOW</literal> statement. For
- information on interpreting the output of a query on the
- <literal>INFORMATION_SCHEMA</literal>, please see
- <xref linkend="indexing-displaying-show" />.
- </para>
-
- <para>
- One advantage of the <literal>INFORMATION_SCHEMA</literal> is that
- you can view the index information of more than one table at a time
- by modifying the <literal>TABLE_NAME</literal> portion of the
- <literal>WHERE</literal> clause of your query.
- </para>
+ <remark>
+ [MH] UPDATEME WHEN SAMPLE DB IS FINISHED
+ </remark>
+
+ <para>
+ The output of a query to the
+ <literal>INFORMATION_SCHEMA</literal> closely matches that of
+ the <literal>SHOW</literal> statement. For information on
+ interpreting the output of a query on the
+ <literal>INFORMATION_SCHEMA</literal>, please see
+ <xref linkend="indexing-displaying-show" />.
+ </para>
+
+ <para>
+ One advantage of the <literal>INFORMATION_SCHEMA</literal> is
+ that you can view the index information of more than one table
+ at a time by modifying the <literal>TABLE_NAME</literal> portion
+ of the <literal>WHERE</literal> clause of your query.
+ </para>
- </section>
+ </section>
<!-- END INFO SCHEMA -->
- <section id="indexing-displaying-administrator">
+ <section id="indexing-displaying-administrator">
- <title>Displaying Index Information Using MySQL Administrator</title>
+ <title>Displaying Index Information Using MySQL Administrator</title>
- <para>
- Users can also view index information using MySQL Administrator. To
- view index information, select the desired schema in the
- <literal>Catalogs</literal> screen, then select the <literal>Schema
- Indices</literal> tab:
- </para>
-
- <figure>
- <title>Displaying index information with MySQL Administrator</title>
- <graphic fileref="images/indexing-displaying-administrator.png" lang="en" format="PNG"/>
- </figure>
-
- <remark>
- [MH] UPDATEME WHEN SAMPLE DB IS FINISHED
- </remark>
-
- <para>
- The <literal>Catalog</literal> screen displays the index information
- for all tables in the selected schema. To view the columns that make
- up a given index, click the arrow icon to the left of the index
- name.
- </para>
+ <para>
+ Users can also view index information using MySQL Administrator.
+ To view index information, select the desired schema in the
+ <literal>Catalogs</literal> screen, then select the
+ <literal>Schema Indices</literal> tab:
+ </para>
+
+ <figure>
+ <title>Displaying index information with MySQL Administrator</title>
+ <graphic fileref="images/indexing-displaying-administrator.png" lang="en" format="PNG"/>
+ </figure>
+
+ <remark>
+ [MH] UPDATEME WHEN SAMPLE DB IS FINISHED
+ </remark>
+
+ <para>
+ The <literal>Catalog</literal> screen displays the index
+ information for all tables in the selected schema. To view the
+ columns that make up a given index, click the arrow icon to the
+ left of the index name.
+ </para>
- </section>
+ </section>
<!-- END ADMINISTRATOR -->
- </section>
+ </section>
<!-- END DISPLAYING -->
- <section id="indexing-creating">
+ <section id="indexing-creating">
- <title>Creating Indexes</title>
+ <title>Creating Indexes</title>
- <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, specifying the index information either as part of a
- column information or at the end of the column definitions.
- </para>
-
- <para>
- At minimum, the <literal>PRIMARY KEY</literal> and
- <literal>UNIQUE</literal> indexes should be specified at table
- creation to prevent duplicate key issues from occurring when the
- indexes added later (if you try to add such an index after your
- table is populated, you may have to remove duplicate rows manually
- before the indexes can be created).
- </para>
-
- <para>
- For example, this is a simplified version of the CREATE TABLE
- statement for the <literal>inventory</literal> table:
- </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, specifying the index information either as part of a
+ column information or at the end of the column definitions.
+ </para>
+
+ <para>
+ At minimum, the <literal>PRIMARY KEY</literal> and
+ <literal>UNIQUE</literal> indexes should be specified at table
+ creation to prevent duplicate key issues from occurring when the
+ indexes added later (if you try to add such an index after your
+ table is populated, you may have to remove duplicate rows
+ manually before the indexes can be created).
+ </para>
+
+ <para>
+ For example, this is a simplified version of the CREATE TABLE
+ statement for the <literal>inventory</literal> table:
+ </para>
<programlisting>
<userinput>
@@ -621,20 +679,22 @@
</userinput>
</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 necessary to specify index information at the end of the
- <literal>CREATE TABLE</literal> statement if the index contains more
- than 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>
+ <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 necessary to specify index information at the end of the
+ <literal>CREATE TABLE</literal> statement if the index contains
+ more than 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>
<userinput>
@@ -646,105 +706,108 @@
</userinput>
</programlisting>
- </section>
+ </section>
<!-- END CREATE TABLE -->
- <section id="indexing-creating-create-index">
+ <section id="indexing-creating-create-index">
- <title>Using the CREATE INDEX Syntax</title>
+ <title>Using the CREATE INDEX Syntax</title>
- <para>
- To create a table with the <literal>CREATE INDEX</literal> syntax,
- you specify the type of index, a name for the index, the table to
- create the index on, and a list of the columns that form the index.
- <literal>CREATE INDEX</literal> is a non-standard alternative syntax
- to <literal>ALTER TABLE</literal>, described in the section that
- follows.
- </para>
-
- <para>
- For example, to create an index on the <literal>last_name</literal>
- and <literal>first_name</literal> columns of the actor table, you
- would execute the following statement:
- </para>
+ <para>
+ To create a table with the <literal>CREATE INDEX</literal>
+ syntax, you specify the type of index, a name for the index, the
+ table to create the index on, and a list of the columns that
+ form the index. <literal>CREATE INDEX</literal> is a
+ non-standard alternative syntax to <literal>ALTER
+ TABLE</literal>, described in the section that follows.
+ </para>
+
+ <para>
+ For example, to create an index on the
+ <literal>last_name</literal> and <literal>first_name</literal>
+ columns of the actor table, you would execute the following
+ statement:
+ </para>
<programlisting>
<userinput>CREATE INDEX last_first_name ON actor (last_name, first_name)</userinput>
</programlisting>
- <para>
- This next 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>
+ <para>
+ This next 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>
<userinput>CREATE UNIQUE INDEX unique_manager ON store (manager)</userinput>
</programlisting>
- <para>
- The index types can be <literal>INDEX</literal>, <literal>UNIQUE
- INDEX</literal>, <literal>SPATIAL INDEX</literal>, and
- <literal>FULLTEXT INDEX</literal>.
- </para>
+ <para>
+ The index types can be <literal>INDEX</literal>, <literal>UNIQUE
+ INDEX</literal>, <literal>SPATIAL INDEX</literal>, and
+ <literal>FULLTEXT INDEX</literal>.
+ </para>
- </section>
+ </section>
<!-- END CREATE INDEX -->
- <section id="indexing-creating-alter-table">
+ <section id="indexing-creating-alter-table">
- <title>Using ALTER TABLE to Create Indexes</title>
+ <title>Using ALTER TABLE to Create Indexes</title>
- <para>
- You can use the <literal>ALTER TABLE</literal> statement to create
- indexes on existing tables. The benefit of <literal>ALTER
- TABLE</literal> is that it can be used to create multiple indexes in
- a single statement, which can speed index creation when multiple
- indexes are needed.
- </para>
-
- <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>
+ <para>
+ You can use the <literal>ALTER TABLE</literal> statement to
+ create indexes on existing tables. The benefit of <literal>ALTER
+ TABLE</literal> is that it can be used to create multiple
+ indexes in a single statement, which can speed index creation
+ when multiple indexes are needed.
+ </para>
+
+ <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>
<userinput>ALTER TABLE actor ADD INDEX last_first_name (last_name, first_name)</userinput>
</programlisting>
- <para>
- This example creates an index that enforces uniqueness on the
- <literal>manager</literal> column of the <literal>store</literal>:
- </para>
+ <para>
+ This example creates an index that enforces uniqueness on the
+ <literal>manager</literal> column of the
+ <literal>store</literal>:
+ </para>
<programlisting>
<userinput>ALTER TABLE actor ADD UNIQUE unique_manager (manager)</userinput>
</programlisting>
- <para>
- Here is an example of creating a primary key on the
- <literal>staff</literal> table:
- </para>
+ <para>
+ Here is an example of creating a primary key on the
+ <literal>staff</literal> table:
+ </para>
<programlisting>
<userinput>ALTER TABLE staff ADD PRIMARY KEY (staff_id)</userinput>
</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>
-
- <para>
- Multiple indexes can be created with a single <literal>ALTER
- TABLE</literal> statement by separating the <literal>ADD statements
- with commas:</literal>
- </para>
+ <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>
+
+ <para>
+ Multiple indexes can be created with a single <literal>ALTER
+ TABLE</literal> statement by separating the <literal>ADD
+ statements with commas:</literal>
+ </para>
<programlisting>
<userinput>
@@ -752,43 +815,43 @@
</userinput>
</programlisting>
- <para>
- Other index types you can add include <literal>ADD SPATIAL</literal>
- and <literal>ADD FULLTEXT</literal>.
- </para>
+ <para>
+ Other index types you can add include <literal>ADD
+ SPATIAL</literal> and <literal>ADD FULLTEXT</literal>.
+ </para>
- </section>
+ </section>
<!-- END ALTER TABLE -->
- <section id="indexing-creating-prefix">
+ <section id="indexing-creating-prefix">
- <title>Indexing the Prefix of a Column</title>
+ <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>
+ <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>
<userinput>CREATE INDEX lname ON actor (last_name(5))</userinput>
</programlisting>
- <para>
- Indexing the prefix of a column decreases the size of the index on
- disk 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>
+ <para>
+ Indexing the prefix of a column decreases the size of the index
+ on disk 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>
<userinput>
@@ -798,33 +861,33 @@
</userinput>
</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>
+ <para>
+ Start with a <replaceable>N</replaceable> value of
+ <literal>3</literal> and increase the size of
+ <replaceable>N</replaceable> until the value of
+ <literal>prefix_distinct</literal> nears that of
+ <literal>distinct_rows</literal>.
+ </para>
- </section>
+ </section>
<!-- END PREFIX -->
- </section>
+ </section>
<!-- END CREATING -->
- <section id="indexing-composite">
+ <section id="indexing-composite">
- <title>Creating and Using Composite Indexes</title>
+ <title>Creating and Using Composite Indexes</title>
- <para>
- When executing a <literal>SELECT</literal> query, the MySQL server
- typically uses only 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>
+ <para>
+ When executing a <literal>SELECT</literal> query, the MySQL server
+ typically uses only 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>
<userinput>
@@ -835,36 +898,36 @@
</userinput>
</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>
+ <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>
<userinput>CREATE INDEX last_first_name ON actor (last_name, first_name)</userinput>
</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>
+ <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>
<userinput>
@@ -874,10 +937,10 @@
</userinput>
</programlisting>
- <para>
- However, the following query would not make use of our composite
- index:
- </para>
+ <para>
+ However, the following query would not make use of our composite
+ index:
+ </para>
<programlisting>
<userinput>
@@ -887,46 +950,47 @@
</userinput>
</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>
+ <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>
+ </section>
<!-- END COMPOSITE -->
- <section id="indexing-dropping">
+ <section id="indexing-dropping">
- <title>Dropping Indexes</title>
+ <title>Dropping Indexes</title>
- <para>
- Existing indexes can be dropped using either a <literal>DROP
- INDEX</literal> or <literal>ALTER TABLE</literal> syntax:
- </para>
+ <para>
+ Existing indexes can be dropped using either a <literal>DROP
+ INDEX</literal> or <literal>ALTER TABLE</literal> syntax:
+ </para>
<programlisting>
<userinput>DROP INDEX <replaceable>index_name</replaceable> ON <replaceable>table_name</replaceable></userinput>
@@ -934,70 +998,71 @@
<userinput>ALTER TABLE <replaceable>table_name</replaceable> DROP INDEX <replaceable>index_name</replaceable></userinput>
</programlisting>
- <para>
- You can drop multiple indexes in a single <literal>ALTER
- TABLE</literal> statement by separating them with commas:
- </para>
+ <para>
+ You can drop multiple indexes in a single <literal>ALTER
+ TABLE</literal> statement by separating them with commas:
+ </para>
<programlisting>
<userinput>ALTER TABLE actor DROP PRIMARY KEY, DROP INDEX last_first_name</userinput>
</programlisting>
- </section>
+ </section>
<!-- END DROPPING -->
- <section id="indexing-fulltext">
+ <section id="indexing-fulltext">
- <title>Using FULLTEXT Indexes</title>
+ <title>Using FULLTEXT Indexes</title>
- <para>
- While regular indexes are effective for many purposes, they are not
- effective for columns that contain natural language. An index can be
- used for single word <literal>CHAR</literal> and
- <literal>VARCHAR</literal> columns, but <literal>FULLTEXT</literal>
- indexes are designed for finding strings within larger natural
- language fields.
- </para>
-
- <para>
- A <literal>FULLTEXT</literal> search takes a string and column list
- and searches the specified columns for the string, returning results
- ranked by relevancy.
- </para>
-
- <para>
- The <literal>FULLTEXT</literal> index is available for the
- <literal>MyISAM</literal> storage engine only.
- </para>
-
- <para>
- The syntax for creating a <literal>FULLTEXT</literal> index is listed
- in <xref linkend="indexing-creating" />. Once the index is created,
- the <literal>MATCH ... AGAINST</literal> syntax can be used to
- perform <literal>FULLTEXT</literal> queries.
- </para>
-
- <para>
- The <literal>MATCH</literal> clause indicates which columns are to be
- searched. The list of columns in the <literal>MATCH</literal> clause
- must be identical to the list of columns in the
- <literal>FULLTEXT</literal> index.
- </para>
-
- <para>
- The <literal>AGAINST</literal> clause contains the string being
- searched for. The string in the <literal>AGAINST</literal> clause
- must be a constant string: you cannot use a user variable or search
- result in the <literal>AGAINST</literal> clause.
- </para>
-
- <para>
- Here is an example of a basic <literal>FULLTEXT</literal> query that
- searches for movies in the <literal>film</literal> table that contain
- the word <literal>army</literal> in the <literal>title</literal> or
- <literal>description</literal> columns:
- </para>
+ <para>
+ While regular indexes are effective for many purposes, they are
+ not effective for columns that contain natural language. An index
+ can be used for single word <literal>CHAR</literal> and
+ <literal>VARCHAR</literal> columns, but
+ <literal>FULLTEXT</literal> indexes are designed for finding
+ strings within larger natural language fields.
+ </para>
+
+ <para>
+ A <literal>FULLTEXT</literal> search takes a string and column
+ list and searches the specified columns for the string, returning
+ results ranked by relevancy.
+ </para>
+
+ <para>
+ The <literal>FULLTEXT</literal> index is available for the
+ <literal>MyISAM</literal> storage engine only.
+ </para>
+
+ <para>
+ The syntax for creating a <literal>FULLTEXT</literal> index is
+ listed in <xref linkend="indexing-creating" />. Once the index is
+ created, the <literal>MATCH ... AGAINST</literal> syntax can be
+ used to perform <literal>FULLTEXT</literal> queries.
+ </para>
+
+ <para>
+ The <literal>MATCH</literal> clause indicates which columns are to
+ be searched. The list of columns in the <literal>MATCH</literal>
+ clause must be identical to the list of columns in the
+ <literal>FULLTEXT</literal> index.
+ </para>
+
+ <para>
+ The <literal>AGAINST</literal> clause contains the string being
+ searched for. The string in the <literal>AGAINST</literal> clause
+ must be a constant string: you cannot use a user variable or
+ search result in the <literal>AGAINST</literal> clause.
+ </para>
+
+ <para>
+ Here is an example of a basic <literal>FULLTEXT</literal> query
+ that searches for movies in the <literal>film</literal> table that
+ contain the word <literal>army</literal> in the
+ <literal>title</literal> or <literal>description</literal>
+ columns:
+ </para>
<programlisting>
<userinput>SELECT title, description FROM film WHERE MATCH (title, description) AGAINST ('army')</userinput>
@@ -1009,16 +1074,16 @@
1 row in set (0.00 sec)
</programlisting>
- <para>
- Results from a query with <literal>MATCH ... AGAINST</literal> in the
- <literal>WHERE</literal> clause will always return in descending
- order based on relevancy.
- </para>
-
- <para>
- Here is the same query performed with a <literal>LIKE</literal>
- clause instead:
- </para>
+ <para>
+ Results from a query with <literal>MATCH ... AGAINST</literal> in
+ the <literal>WHERE</literal> clause will always return in
+ descending order based on relevancy.
+ </para>
+
+ <para>
+ Here is the same query performed with a <literal>LIKE</literal>
+ clause instead:
+ </para>
<programlisting>
<userinput>SELECT title, description FROM film WHERE title LIKE '%army%' OR description LIKE '%army%'</userinput>
@@ -1030,15 +1095,15 @@
1 row in set (0.20 sec)
</programlisting>
- <para>
- Note the performance improvement provided by the
- <literal>FULLTEXT</literal> index.
- </para>
-
- <para>
- The <literal>MATCH ... AGAINST</literal> syntax can also provide
- relevancy ranking information:
- </para>
+ <para>
+ Note the performance improvement provided by the
+ <literal>FULLTEXT</literal> index.
+ </para>
+
+ <para>
+ The <literal>MATCH ... AGAINST</literal> syntax can also provide
+ relevancy ranking information:
+ </para>
<programlisting>
<userinput>
@@ -1054,39 +1119,40 @@
1 row in set (0.00 sec)
</programlisting>
- <para>
- The relevancy scores are based on the weighting of words within the
- individual rows. Words that occur rarely in the table are ranked
- higher than words that appear in a large percentage of the rows.
- </para>
-
- <para>
- For more information on the <literal>FULLTEXT</literal> search
- engine, see the
- <ulink url="http://dev.mysql.com/doc/mysql/en/fulltext-search.html">Fulltext
- Search</ulink> section of the MySQL Reference Manual.
- </para>
+ <para>
+ The relevancy scores are based on the weighting of words within
+ the individual rows. Words that occur rarely in the table are
+ ranked higher than words that appear in a large percentage of the
+ rows.
+ </para>
+
+ <para>
+ For more information on the <literal>FULLTEXT</literal> search
+ engine, see the
+ <ulink url="http://dev.mysql.com/doc/mysql/en/fulltext-search.html">Fulltext
+ Search</ulink> section of the MySQL Reference Manual.
+ </para>
- </section>
+ </section>
<!-- END FULLTEXT -->
- <section id="indexing-explain">
+ <section id="indexing-explain">
- <title>Using EXPLAIN to Optimize Indexing</title>
+ <title>Using EXPLAIN to Optimize Indexing</title>
- <para>
- Sometimes it is not easy to identify which columns of a table to
- index, even when you have identified the slow queries in your
- application. The <literal>EXPLAIN</literal> statement is designed to
- assist in the query optimization process by providing insight into
- how the MySQL optimizer handles a specific query.
- </para>
-
- <para>
- To analyze a query, precede the query with the
- <literal>EXPLAIN</literal> keyword:
- </para>
+ <para>
+ Sometimes it is not easy to identify which columns of a table to
+ index, even when you have identified the slow queries in your
+ application. The <literal>EXPLAIN</literal> statement is designed
+ to assist in the query optimization process by providing insight
+ into how the MySQL optimizer handles a specific query.
+ </para>
+
+ <para>
+ To analyze a query, precede the query with the
+ <literal>EXPLAIN</literal> keyword:
+ </para>
<programlisting>
<userinput>
@@ -1131,31 +1197,32 @@
3 rows in set (0.00 sec)
</programlisting>
- <para>
- <literal>EXPLAIN</literal> returns a row of information for each
- table used in the <literal>SELECT</literal> statement. The tables are
- listed in the output in the order that MySQL would read them while
- processing the query.
- </para>
-
- <para>
- The main things to look out for are rows where the
- <literal>key</literal> column is <literal>NULL</literal>, where the
- <literal>type</literal> column is <literal>range</literal>,
- <literal>index</literal>, or <literal>ALL</literal>, or where the
- <literal>Extra</literal> column contains <literal>Using
- filesort</literal> or <literal>Using temporary</literal>. Such
- queries should be closely examined for index usage.
- </para>
-
- <para>
- For additional information on using the <literal>EXPLAIN</literal>
- statement, see the
- <ulink url="http://dev.mysql.com/doc/mysql/en/explain.html"><literal>EXPLAIN</literal></ulink>
- section of the MySQL Reference Manual.
- </para>
+ <para>
+ <literal>EXPLAIN</literal> returns a row of information for each
+ table used in the <literal>SELECT</literal> statement. The tables
+ are listed in the output in the order that MySQL would read them
+ while processing the query.
+ </para>
+
+ <para>
+ The main things to look out for are rows where the
+ <literal>key</literal> column is <literal>NULL</literal>, where
+ the <literal>type</literal> column is <literal>range</literal>,
+ <literal>index</literal>, or <literal>ALL</literal>, or where the
+ <literal>Extra</literal> column contains <literal>Using
+ filesort</literal> or <literal>Using temporary</literal>. Such
+ queries should be closely examined for proper index usage as they
+ generally indicate that no index is being used.
+ </para>
+
+ <para>
+ For additional information on using the <literal>EXPLAIN</literal>
+ statement, see the
+ <ulink url="http://dev.mysql.com/doc/mysql/en/explain.html"><literal>EXPLAIN</literal></ulink>
+ section of the MySQL Reference Manual.
+ </para>
- </section>
+ </section>
<!-- END EXPLAIN -->
--- 1.1/userguide/installing.xml 2005-06-08 15:44:32 -06:00
+++ 1.2/userguide/installing.xml 2005-07-20 09:51:23 -06:00
@@ -9,7 +9,13 @@
<title>Introduction</title>
- <para></para>
+ <para>Unless you have been provided with a working copy of MySQL from your ISP or employer, the first step in using MySQL is to install MySQL on your local machine or server.</para>
+
+ <para>MySQL is available pre-compiled and packaged for a wide variety of platforms including <literal>Microsoft Windows</literal>, <literal>Linux</literal>, <literal>Solaris</literal>, <literal>FreeBSD</literal>, and <literal>Mac OS X</literal> to name a few.</para>
+
+ <para>The installation process varies by platform but generally involves downloading an installer or compressed archive, extracting or executing the downloaded file, and then configuring and starting the MySQL server. Once the MySQL server is successfully installed and configured, you can download and install the MySQL GUI tools to manage and query your new server.</para>
+
+ <para>In the sections that follow we will explain the process described above with regards to the more popular platforms used with MySQL, namely Microsoft Windows, Linux, and Mac OS X.</para>
</section>
@@ -19,8 +25,17 @@
<title>Downloading MySQL</title>
- <para></para>
+ <para>All binary and source versions of MySQL are available at <ulink url="http://dev.mysql.com/downloads/" />. We strongly recommend you download the latest stable version of MySQL that is available for your platform (currently <ulink url="http://dev.mysql.com/downloads/mysql/5.0.html">MySQL 5.0.X</ulink>, which this userguide is based on).</para>
+
+ <para>This chapter will deal with installation from pre-compiled binaries only and it is recommended that you download the same. If you want information on compiling and installing MySQL from source code, please refer to the <ulink url="http://dev.mysql.com/doc/mysql/en/installing-source.html">MySQL Installation Using a Source Distribution</ulink> section of the MySQL Reference Manual.</para>
+
+
+<para>The binaries you download will depend on the platform you intend to use. For specific information please refer to the appropriate section that follows.</para>
+<section id="installing-downloading-windows">
+ <title>Downloading MySQL for Microsoft Windows</title>
+ <para></para>
+</section> <!-- END WINDOWS DOWNLOAD -->
</section>
<!-- END DOWNLOADING -->
--- 1.57/refman-4.1/news.xml 2005-07-19 09:57:23 -06:00
+++ 1.58/refman-4.1/news.xml 2005-07-20 09:51:22 -06:00
@@ -414,6 +414,22 @@
</para>
</listitem>
+ <listitem>
+ <para>
+ <literal>DROP TRIGGER</literal> now requires a schema name
+ instead of a table name (schema name is optional and, if
+ omitted, the current schema will be used).
+ </para>
+
+ <para>
+ <emphasis>Note:</emphasis> When upgrading from a previous
+ version of MySQL 5 to MySQL 5.0.10 or newer, you must drop
+ all triggers before upgrading and re-create them after or
+ <literal>DROP TRIGGER</literal> will not work after the
+ upgrade. (Bug #5892)
+ </para>
+ </listitem>
+
</itemizedlist>
<para>
@@ -3115,10 +3131,9 @@
<listitem>
<para>
- InnoDB: Relaxed locking in
- <literal>INSERT…SELECT</literal>, single table
- <literal>UPDATE…SELECT</literal> and single table
- <literal>DELETE…SELECT</literal> clauses when
+ InnoDB: Relaxed locking in <literal>INSERT…SELECT</literal>,
+ single table <literal>UPDATE…SELECT</literal> and single
+ table <literal>DELETE…SELECT</literal> clauses when
<literal>innodb_locks_unsafe_for_binlog</literal> is used
and isolation level of the transaction is not serializable.
<literal>InnoDB</literal> uses consistent read in these
@@ -26787,9 +26802,9 @@
<para>
Changed <literal>LIKE</literal> character comparison to
behave as <literal>=</literal>; This means that <literal>'e'
- LIKE 'é'</literal> is now true. (If the line doesn't
- display correctly, the latter 'e' is a French 'e' with an
- acute accent above.)
+ LIKE 'é'</literal> is now true. (If the line doesn't display
+ correctly, the latter 'e' is a French 'e' with an acute
+ accent above.)
</para>
</listitem>
--- 1.66/refman-5.0/news.xml 2005-07-19 09:57:34 -06:00
+++ 1.67/refman-5.0/news.xml 2005-07-20 09:51:23 -06:00
@@ -260,6 +260,22 @@
</para>
</listitem>
+ <listitem>
+ <para>
+ <literal>DROP TRIGGER</literal> now requires a schema name
+ instead of a table name (schema name is optional and, if
+ omitted, the current schema will be used).
+ </para>
+
+ <para>
+ <emphasis>Note:</emphasis> When upgrading from a previous
+ version of MySQL 5 to MySQL 5.0.10 or newer, you must drop
+ all triggers before upgrading and re-create them after or
+ <literal>DROP TRIGGER</literal> will not work after the
+ upgrade. (Bug #5892)
+ </para>
+ </listitem>
+
</itemizedlist>
<para>
@@ -270,6 +286,64 @@
<listitem>
<para>
+ Creating a trigger in one database that references a table
+ in another database was being allowed without generating
+ errors. (Bug #8751)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Duplicate trigger names were allowed within a single schema.
+ (Bug #6182)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Server did not accept some fully-qualified trigger names.
+ (Bug #8758)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The <literal>traditional</literal> SQL mode accepted invalid
+ dates if the date value provided was the result of an
+ implicit type conversion. (Bug #5906)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The MySQL server had issues with certain combinations of
+ basedir and datadir. (Bug #7249)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>INFORMATION_SCHEMA.COLUMNS</literal> had some
+ inaccurate values for some data types. (Bug #11057)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ LIKE pattern matching using prefix index didn't return
+ correct result. (Bug #11650)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Server had access denied errors for default accounts on
+ machines where 127.0.0.1 != 'localhost'. (Bug #11822)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
<literal>SHOW BINARY LOGS</literal> displayed a file size of
0 for all log files but the current one if the files were
not located in the data directory. (Bug #12004)
@@ -4223,10 +4297,9 @@
<listitem>
<para>
- InnoDB: Relaxed locking in
- <literal>INSERT…SELECT</literal>, single table
- <literal>UPDATE…SELECT</literal> and single table
- <literal>DELETE…SELECT</literal> clauses when
+ InnoDB: Relaxed locking in <literal>INSERT…SELECT</literal>,
+ single table <literal>UPDATE…SELECT</literal> and single
+ table <literal>DELETE…SELECT</literal> clauses when
<literal>innodb_locks_unsafe_for_binlog</literal> is used
and isolation level of the transaction is not serializable.
<literal>InnoDB</literal> uses consistent read in these
--- 1.8/refman-5.0/triggers.xml 2005-07-19 09:57:36 -06:00
+++ 1.9/refman-5.0/triggers.xml 2005-07-20 09:51:23 -06:00
@@ -149,13 +149,25 @@
<!-- There is no IF EXISTS clause, apparently. -->
<programlisting>
-DROP TRIGGER <replaceable>tbl_name</replaceable>.<replaceable>trigger_name</replaceable>
+DROP TRIGGER [<replaceable>schema</replaceable>.]<replaceable>trigger_name</replaceable>
</programlisting>
<para>
- Drops a trigger. The name of the trigger to drop must include the
- table name because each trigger is associated with a particular
- table.
+ Drops a trigger. The schema is optional. If the schema is omitted,
+ the trigger is dropped from the current schema.
+ </para>
+
+ <para>
+ Prior to MySQL 5.0.10, the table name was required instead of the
+ schema (<literal><replaceable>table_name</replaceable>.xreplace
+ trigger_name</literal>).
+ </para>
+
+ <para>
+ <emphasis>Note:</emphasis> When upgrading from a previous version
+ of MySQL 5 to MySQL 5.0.10 or newer, you must drop all triggers
+ before upgrading and re-create them after or <literal>DROP TRIGGER
+ will not work after the upgrade.</literal>
</para>
<para>
--- 1.80/refman/news.xml 2005-07-19 09:57:44 -06:00
+++ 1.81/refman/news.xml 2005-07-20 09:51:23 -06:00
@@ -253,6 +253,22 @@
</para>
</listitem>
+ <listitem>
+ <para>
+ <literal>DROP TRIGGER</literal> now requires a schema name
+ instead of a table name (schema name is optional and, if
+ omitted, the current schema will be used).
+ </para>
+
+ <para>
+ <emphasis>Note:</emphasis> When upgrading from a previous
+ version of MySQL 5 to MySQL 5.0.10 or newer, you must drop
+ all triggers before upgrading and re-create them after or
+ <literal>DROP TRIGGER</literal> will not work after the
+ upgrade. (Bug #5892)
+ </para>
+ </listitem>
+
</itemizedlist>
<para>
@@ -263,6 +279,64 @@
<listitem>
<para>
+ Creating a trigger in one database that references a table
+ in another database was being allowed without generating
+ errors. (Bug #8751)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Duplicate trigger names were allowed within a single schema.
+ (Bug #6182)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Server did not accept some fully-qualified trigger names.
+ (Bug #8758)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The <literal>traditional</literal> SQL mode accepted invalid
+ dates if the date value provided was the result of an
+ implicit type conversion. (Bug #5906)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The MySQL server had issues with certain combinations of
+ basedir and datadir. (Bug #7249)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>INFORMATION_SCHEMA.COLUMNS</literal> had some
+ inaccurate values for some data types. (Bug #11057)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ LIKE pattern matching using prefix index didn't return
+ correct result. (Bug #11650)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ Server had access denied errors for default accounts on
+ machines where 127.0.0.1 != 'localhost'. (Bug #11822)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
<literal>SHOW BINARY LOGS</literal> displayed a file size of
0 for all log files but the current one if the files were
not located in the data directory. (Bug #12004)
@@ -4216,10 +4290,9 @@
<listitem>
<para>
- InnoDB: Relaxed locking in
- <literal>INSERT…SELECT</literal>, single table
- <literal>UPDATE…SELECT</literal> and single table
- <literal>DELETE…SELECT</literal> clauses when
+ InnoDB: Relaxed locking in <literal>INSERT…SELECT</literal>,
+ single table <literal>UPDATE…SELECT</literal> and single
+ table <literal>DELETE…SELECT</literal> clauses when
<literal>innodb_locks_unsafe_for_binlog</literal> is used
and isolation level of the transaction is not serializable.
<literal>InnoDB</literal> uses consistent read in these
@@ -6634,6 +6707,27 @@
<listitem>
<para>
+ LIKE pattern matching using prefix index didn't return
+ correct result. (Bug #11650)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ The MySQL server had issues with certain combinations of
+ basedir and datadir. (Bug #7249)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
+ <literal>mysql_next_result()</literal> returns incorrect
+ value if final query in a batch fails. (Bug #12001)
+ </para>
+ </listitem>
+
+ <listitem>
+ <para>
<literal>SHOW BINARY LOGS</literal> displayed a file size of
0 for all log files but the current one if the files were
not located in the data directory. (Bug #12004)
@@ -7672,11 +7766,10 @@
and isolation level of the transaction is not set to
serializable then <literal>InnoDB</literal> uses a
consistent read for select in clauses like <literal>INSERT
- INTO…SELECT</literal> and
- <literal>UPDATE…(SELECT)</literal> that do not specify
- <literal>FOR UPDATE</literal> or <literal>IN SHARE
- MODE</literal>. Thus no locks are set to rows read from
- selected table.
+ INTO…SELECT</literal> and <literal>UPDATE…(SELECT)</literal>
+ that do not specify <literal>FOR UPDATE</literal> or
+ <literal>IN SHARE MODE</literal>. Thus no locks are set to
+ rows read from selected table.
</para>
</listitem>
@@ -9477,10 +9570,9 @@
<listitem>
<para>
- InnoDB: Relaxed locking in
- <literal>INSERT…SELECT</literal>, single table
- <literal>UPDATE…SELECT</literal> and single table
- <literal>DELETE…SELECT</literal> clauses when
+ InnoDB: Relaxed locking in <literal>INSERT…SELECT</literal>,
+ single table <literal>UPDATE…SELECT</literal> and single
+ table <literal>DELETE…SELECT</literal> clauses when
<literal>innodb_locks_unsafe_for_binlog</literal> is used
and isolation level of the transaction is not serializable.
<literal>InnoDB</literal> uses consistent read in these
@@ -33143,9 +33235,9 @@
<para>
Changed <literal>LIKE</literal> character comparison to
behave as <literal>=</literal>; This means that <literal>'e'
- LIKE 'é'</literal> is now true. (If the line doesn't
- display correctly, the latter 'e' is a French 'e' with an
- acute accent above.)
+ LIKE 'é'</literal> is now true. (If the line doesn't display
+ correctly, the latter 'e' is a French 'e' with an acute
+ accent above.)
</para>
</listitem>
--- 1.8/refman/triggers.xml 2005-07-19 09:57:47 -06:00
+++ 1.9/refman/triggers.xml 2005-07-20 09:51:23 -06:00
@@ -149,13 +149,25 @@
<!-- There is no IF EXISTS clause, apparently. -->
<programlisting>
-DROP TRIGGER <replaceable>tbl_name</replaceable>.<replaceable>trigger_name</replaceable>
+DROP TRIGGER [<replaceable>schema</replaceable>.]<replaceable>trigger_name</replaceable>
</programlisting>
<para>
- Drops a trigger. The name of the trigger to drop must include the
- table name because each trigger is associated with a particular
- table.
+ Drops a trigger. The schema is optional. If the schema is omitted,
+ the trigger is dropped from the current schema.
+ </para>
+
+ <para>
+ Prior to MySQL 5.0.10, the table name was required instead of the
+ schema (<literal><replaceable>table_name</replaceable>.xreplace
+ trigger_name</literal>).
+ </para>
+
+ <para>
+ <emphasis>Note:</emphasis> When upgrading from a previous version
+ of MySQL 5 to MySQL 5.0.10 or newer, you must drop all triggers
+ before upgrading and re-create them after or <literal>DROP TRIGGER
+ will not work after the upgrade.</literal>
</para>
<para>
| Thread |
|---|
| • bk commit - mysqldoc@docsrva tree (Mike.Hillyer:1.3070) | mhillyer | 20 Jul |
