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.2885 05/06/28 15:06:27 Mike.Hillyer@stripped +4 -0
Some userguide work.
userguide/glossary.xml
1.1 05/06/28 15:06:26 Mike.Hillyer@stripped +62 -0
userguide/userguide.xml
1.5 05/06/28 15:06:26 Mike.Hillyer@stripped +21 -8
Fix part names, add glossary section to xincludes.
userguide/indexing.xml
1.7 05/06/28 15:06:26 Mike.Hillyer@stripped +274 -585
Reformat, apply more of stefan's suggestions. Work on EXPLAIN section that is short but not too short.
userguide/glossary.xml
1.0 05/06/28 15:06:26 Mike.Hillyer@stripped +0 -0
BitKeeper file /home/mysqldoc/mysqldoc/userguide/glossary.xml
userguide/disaster.xml
1.2 05/06/28 15:06:26 Mike.Hillyer@stripped +7 -1
Make disaster chapter validate
# 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.1/userguide/disaster.xml 2005-06-13 14:56:27 -06:00
+++ 1.2/userguide/disaster.xml 2005-06-28 15:06:26 -06:00
@@ -5,7 +5,13 @@
<title>Disaster Prevention and Recovery</title>
-
+ <section id="disaster-introduction">
+
+ <para>
+ This is the disaster prevention and recovery chapter.
+ </para>
+
+ </section>
</chapter>
<!-- END DISASTER PREVENTION CHAPTER -->
--- New file ---
+++ userguide/glossary.xml 05/06/28 15:06:26
<?xml version="1.0" encoding="ISO-8859-1"?>
<!DOCTYPE glossary PUBLIC "-//OASIS//DTD DocBook XML V4.3//EN"
"http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd">
<glossary id="glossary">
<title>Glossary</title>
<para>
The following terms appear at various locations within the MySQL User
Guide.
</para>
<glossdiv>
<title>A</title>
<glossentry id="glossary-active-schema">
<glossterm>Active Schema</glossterm>
<glossdef>
<para>
The schema against which all queries and statements are executed by
default. The active schema is set by using the
<literal>USE</literal> keyword or by double-clicking on a schema
using the MySQL Query Browser.
</para>
<glossseealso otherterm="glossary-schema">Schema</glossseealso>
</glossdef>
</glossentry>
</glossdiv>
<glossdiv>
<title>S</title>
<glossentry id="glossary-schema">
<glossterm>Schema</glossterm>
<glossdef>
<para>
A named collection of tables, stored procedures, views, and other
objects, also referred to as a <literal>database</literal>.
</para>
<glossseealso otherterm="glossary-active-schema">Active Schema</glossseealso>
</glossdef>
</glossentry>
</glossdiv>
</glossary>
<!-- END GLOSSARY APPENDIX -->
--- 1.6/userguide/indexing.xml 2005-06-25 12:51:54 -06:00
+++ 1.7/userguide/indexing.xml 2005-06-28 15:06:26 -06:00
@@ -16,10 +16,10 @@
<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).
+ 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>
<para>
@@ -58,11 +58,101 @@
</para>
<para>
- In the following sections we will cover what to index, the different
- kinds of index available within MySQL, the steps involved in creating
- and altering indexes, and the process of optimizing indexes.
+ In the following sections we will cover 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>
<!-- END INTRODUCTION -->
@@ -102,15 +192,16 @@
efficiency of querying 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"
- endterm="<literal>MyISAM</literal> storage engine"/> only.
+ <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.
+ <literal>MBRWithin()</literal>. The <literal>SPATIAL</literal> index
+ is available for the <literal>MyISAM</literal> storage engine only.
</para></listitem>
</itemizedlist>
@@ -124,7 +215,9 @@
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.
+ 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>
@@ -149,9 +242,10 @@
<para>
To use indexes effectively, it is necessary to identify those queries
- that are executing slowly and not making effective use of indexes, or which need to contain unique values.
- Slow queries can be identified by directly reviewing the queries you use or
- by using the MySQL Slow Query Log.
+ that are executing slowly and not making effective use of indexes, or
+ which need to 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">
@@ -170,7 +264,7 @@
</para>
<programlisting>
-SELECT last_name, first_name FROM actor WHERE last_name = 'Brando'
+<userinput>SELECT last_name, first_name FROM actor WHERE last_name = 'Brando'</userinput>
</programlisting>
<para>
@@ -184,11 +278,13 @@
</para>
<programlisting>
+<userinput>
SELECT film.title, COUNT(inventory.inventory_id) AS Stock
FROM film, inventory
WHERE film.film_id = inventory.film_id
AND inventory.store_id = 1
GROUP BY film.film_id
+</userinput>
</programlisting>
<para>
@@ -204,12 +300,11 @@
</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.
+ 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>
@@ -229,10 +324,13 @@
<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" />
+ 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>
<!-- END SLOW LOG -->
@@ -249,8 +347,11 @@
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.
+ <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">
@@ -258,15 +359,16 @@
<title>Displaying Table Indexes Using the SHOW Command</title>
<para>
- The <literal>SHOW</literal> command can be used within the active
- schema to display the index information for a table:
+ 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> USE sakila;
+mysql> <userinput>USE sakila;</userinput>
Database changed
-mysql> SHOW INDEX FROM film\G
+mysql> <userinput>SHOW INDEX FROM film\G</userinput>
*************************** 1. row ***************************
Table: film
Non_unique: 0
@@ -359,9 +461,9 @@
</para>
<programlisting>
-mysql> SELECT * FROM INFORMATION_SCHEMA.STATISTICS
- -> WHERE TABLE_SCHEMA = 'sakila'
- -> AND table_name = 'film'\G
+mysql> <userinput>SELECT * FROM INFORMATION_SCHEMA.STATISTICS</userinput>
+ -> <userinput>WHERE TABLE_SCHEMA = 'sakila'</userinput>
+ -> <userinput>AND table_name = 'film'\G</userinput>
*************************** 1. row ***************************
TABLE_CATALOG: NULL
TABLE_SCHEMA: sakila
@@ -490,16 +592,19 @@
<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:
+ 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>
-<programlisting>
-CREATE TABLE <replaceable>table_name</replaceable>(
- <replaceable>column_name</replaceable> <replaceable>column_type</replaceable> [PRIMARY KEY|KEY|UNIQUE KEY],...
- [PRIMARY KEY|INDEX|UNIQUE|FULLTEXT|SPATIAL] <replaceable>index_name</replaceable> (<replaceable>index_col_name</replaceable>,...)
-)
-</programlisting>
+ <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 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
@@ -507,12 +612,14 @@
</para>
<programlisting>
+<userinput>
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)
)
+</userinput>
</programlisting>
<para>
@@ -522,20 +629,22 @@
</para>
<para>
- It is necesscary to specify index information at the end of the
+ It is necessary 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>
+ 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>
CREATE TABLE film_actor (
actor_id INT UNSIGNED NOT NULL,
film_id INT UNSIGNED NOT NULL,
PRIMARY KEY (film_id, actor_id),
)
+</userinput>
</programlisting>
</section>
@@ -547,15 +656,14 @@
<title>Using the CREATE INDEX Syntax</title>
<para>
- The <literal>CREATE INDEX</literal> statement has the following
- syntax:
+ To create a table with the <literal>CREATE TABLE</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 an alternative syntax to
+ <literal>ALTER TABLE</literal>, described in the section that
+ follows.
</para>
-<programlisting>
- CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX <replaceable>index_name</replaceable>
- ON <replaceable>table_name</replaceable> (<replaceable>index_col_name</replaceable>[(<replaceable>prefix length</replaceable>)],...)
- </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
@@ -563,7 +671,7 @@
</para>
<programlisting>
- CREATE INDEX last_first_name ON actor (last_name, first_name);
+ <userinput>CREATE INDEX last_first_name ON actor (last_name, first_name)</userinput>
</programlisting>
<para>
@@ -574,9 +682,15 @@
</para>
<programlisting>
- CREATE UNIQUE INDEX unique_manager ON store (manager);
+ <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>
+
</section>
<!-- END CREATE INDEX -->
@@ -587,14 +701,12 @@
<para>
You can use the <literal>ALTER TABLE</literal> statement to create
- indexes with the following syntax:
+ 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>
-<programlisting>
-ALTER TABLE <replaceable>tbl_name</replaceable> ADD INDEX|UNIQUE|FULLTEXT|SPATIAL [<replaceable>index_name</replaceable>] (<replaceable>index_col_name</replaceable>,...)
-ALTER TABLE <replaceable>tbl_name</replaceable> ADD PRIMARY KEY (<replaceable>index_col_name</replaceable>,...)
-</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
@@ -602,7 +714,7 @@
</para>
<programlisting>
-ALTER TABLE actor ADD INDEX last_first_name (last_name, first_name);
+<userinput>ALTER TABLE actor ADD INDEX last_first_name (last_name, first_name)</userinput>
</programlisting>
<para>
@@ -613,7 +725,7 @@
</para>
<programlisting>
-ALTER TABLE actor ADD UNIQUE unique_manager (manager);
+<userinput>ALTER TABLE actor ADD UNIQUE unique_manager (manager)</userinput>
</programlisting>
<para>
@@ -622,7 +734,7 @@
</para>
<programlisting>
-ALTER TABLE staff ADD PRIMARY KEY (staff_id);
+<userinput>ALTER TABLE staff ADD PRIMARY KEY (staff_id)</userinput>
</programlisting>
<para>
@@ -631,6 +743,11 @@
is no <literal>CREATE INDEX</literal> equivalent for primary keys.
</para>
+ <para>
+ Other index types you can add include <literal>ADD SPATIAL</literal>
+ and <literal>ADD FULLTEXT</literal>.
+ </para>
+
</section>
<!-- END ALTER TABLE -->
@@ -647,7 +764,7 @@
</para>
<programlisting>
- CREATE INDEX lname ON actor (last_name(5));
+ <userinput>CREATE INDEX lname ON actor (last_name(5))</userinput>
</programlisting>
<para>
@@ -664,9 +781,11 @@
</para>
<programlisting>
+<userinput>
SELECT COUNT(DISTINCT <replaceable>column_name</replaceable>) AS distinct_rows,
COUNT(DISTINCT(LEFT(column_name, <replaceable>N</replaceable>))) AS prefix_distinct
FROM <replaceable>table_name</replaceable>
+</userinput>
</programlisting>
<para>
@@ -698,10 +817,12 @@
</para>
<programlisting>
+<userinput>
SELECT actor_id
FROM actor
WHERE last_name = 'Johnson'
AND first_name = 'Robert'
+</userinput>
</programlisting>
<para>
@@ -719,7 +840,7 @@
</para>
<programlisting>
-CREATE INDEX last_first_name ON actor (last_name, first_name);
+<userinput>CREATE INDEX last_first_name ON actor (last_name, first_name)</userinput>
</programlisting>
<para>
@@ -736,9 +857,11 @@
</para>
<programlisting>
+<userinput>
SELECT first_name
FROM actor
WHERE last_name = 'Johnson'
+</userinput>
</programlisting>
<para>
@@ -747,9 +870,11 @@
</para>
<programlisting>
+<userinput>
SELECT last_name
FROM actor
WHERE first_name = 'Robert'
+</userinput>
</programlisting>
<para>
@@ -794,9 +919,9 @@
</para>
<programlisting>
- DROP INDEX <replaceable>index_name</replaceable> ON <replaceable>table_name</replaceable>
-ALTER TABLE <replaceable>table_name</replaceable> DROP PRIMARY KEY
-ALTER TABLE <replaceable>table_name</replaceable> DROP INDEX <replaceable>index_name</replaceable>
+<userinput>DROP INDEX <replaceable>index_name</replaceable> ON <replaceable>table_name</replaceable></userinput>
+<userinput>ALTER TABLE <replaceable>table_name</replaceable> DROP PRIMARY KEY</userinput>
+<userinput>ALTER TABLE <replaceable>table_name</replaceable> DROP INDEX <replaceable>index_name</replaceable></userinput>
</programlisting>
<para>
@@ -805,7 +930,7 @@
</para>
<programlisting>
- ALTER TABLE actor DROP PRIMARY KEY, DROP INDEX last_first_name
+ <userinput>ALTER TABLE actor DROP PRIMARY KEY, DROP INDEX last_first_name</userinput>
</programlisting>
</section>
@@ -865,9 +990,20 @@
</para>
<programlisting>
- SELECT * FROM film WHERE MATCH (title, description) AGAINST ('army')
+ <userinput>SELECT * FROM film WHERE MATCH (title, description) AGAINST ('army')</userinput>
-UPDATEME: SHOW RESULTS WHEN SAMPLE DB IS FINISHED AND POPULATED
+*************************** 1. row ***************************
+ film_id: 40
+ category_id: 6
+ title: ARMY FLINTSTONES
+ description: A Boring Saga of a Database Administrator And a Womanizer who
+must Battle a Waitress in Nigeria
+ rental_duration: 4
+ rental_rate: 0.99
+ length: 148
+replacement_cost: 22.99
+ rating: R
+1 row in set (0.00 sec)
</programlisting>
<para>
@@ -882,11 +1018,24 @@
</para>
<programlisting>
- SELECT * FROM film WHERE title LIKE '%army%' OR description LIKE '%army%'
+ <userinput>SELECT * FROM film WHERE title LIKE '%army%' OR description LIKE '%army%'</userinput>
+
+*************************** 1. row ***************************
+ film_id: 40
+ category_id: 6
+ title: ARMY FLINTSTONES
+ description: A Boring Saga of a Database Administrator And a Womanizer who
+must Battle a Waitress in Nigeria
+ rental_duration: 4
+ rental_rate: 0.99
+ length: 148
+replacement_cost: 22.99
+ rating: R
+1 row in set (0.20 sec)
</programlisting>
<para>
- Note the significant performance inprovement provided by the
+ Note the performance improvement provided by the
<literal>FULLTEXT</literal> index.
</para>
@@ -896,11 +1045,15 @@
</para>
<programlisting>
+ <userinput>
SELECT title, MATCH (title, description) AGAINST ('army') AS rank
FROM film
- WHERE MATCH (title, description) AGAINST ('army')
+ WHERE MATCH (title, description) AGAINST ('army')</userinput>
-UPDATEME: FILL IN RESULTS WHEN SAMPLE DB IS POPULATED
+*************************** 1. row ***************************
+title: ARMY FLINTSTONES
+ rank: 6.1943987015493
+1 row in set (0.00 sec)
</programlisting>
<para>
@@ -938,7 +1091,46 @@
</para>
<programlisting>
- EXPLAIN SELECT something UPDATEME FILL THIS IN
+ <userinput>
+ EXPLAIN SELECT film.title FROM actor, film, film_actor
+ WHERE actor.actor_id = film_actor.actor_id
+ AND film.film_id = film_actor.film_id
+ AND actor.last_name = 'Walken'\G
+ </userinput>
+*************************** 1. row ***************************
+ id: 1
+ select_type: SIMPLE
+ table: actor
+ type: ALL
+possible_keys: PRIMARY
+ key: NULL
+ key_len: NULL
+ ref: NULL
+ rows: 200
+ Extra: Using where
+*************************** 2. row ***************************
+ id: 1
+ select_type: SIMPLE
+ table: film_actor
+ type: ref
+possible_keys: PRIMARY
+ key: PRIMARY
+ key_len: 4
+ ref: sakila.actor.actor_id
+ rows: 26
+ Extra: Using index
+*************************** 3. row ***************************
+ id: 1
+ select_type: SIMPLE
+ table: film
+ type: eq_ref
+possible_keys: PRIMARY
+ key: PRIMARY
+ key_len: 4
+ ref: sakila.film_actor.film_id
+ rows: 1
+ Extra:
+3 rows in set (0.00 sec)
</programlisting>
<para>
@@ -949,523 +1141,20 @@
</para>
<para>
- Each output row from <literal>EXPLAIN</literal> provides information
- about one table, and each row consists of the following columns:
+ 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>
- <itemizedlist>
-
- <listitem><para>
- <literal>id</literal>
- </para>
-
- <para>
- The <literal>SELECT</literal> identifier. This is the sequential
- number of the <literal>SELECT</literal> within the query. The
- <literal>id</literal> is not used when optimizing queries.
- </para></listitem>
-
- <listitem><para>
- <literal>select_type</literal>
- </para>
-
- <para>
- The type of <literal>SELECT</literal>, which can be any of the
- following:
- </para>
-
- <itemizedlist>
-
- <listitem><para>
- <literal>SIMPLE</literal>: Simple <literal>SELECT</literal> query
- (not using <literal>UNION</literal> or subqueries).
- </para></listitem>
-
- <listitem><para>
- <literal>PRIMARY</literal>: Outermost <literal>SELECT</literal>
- query.
- </para></listitem>
-
- <listitem><para>
- <literal>UNION</literal>: Second or later <literal>SELECT</literal>
- statement in a <literal>UNION</literal> query.
- </para></listitem>
-
- <listitem><para>
- <literal>DEPENDENT UNION</literal>: Second or later
- <literal>SELECT</literal> statement in a <literal>UNION</literal>,
- dependent on outer query.
- </para></listitem>
-
- <listitem><para>
- <literal>UNION RESULT</literal>: Result of a
- <literal>UNION</literal> query.
- </para></listitem>
-
- <listitem><para>
- <literal>SUBQUERY</literal>: First <literal>SELECT</literal> in
- subquery.
- </para></listitem>
-
- <listitem><para>
- <literal>DEPENDENT SUBQUERY</literal>: First
- <literal>SELECT</literal> in subquery, dependent on outer query.
- </para></listitem>
-
- <listitem><para>
- <literal>DERIVED</literal>: Derived table <literal>SELECT</literal>
- (subquery in <literal>FROM</literal> clause).
- </para></listitem>
-
- </itemizedlist>
-
- <para>
- The <literal>select_type</literal> value is useful in identifying
- which portion of the source query the table in question is used for,
- but is not the focus of query optimization.
- </para></listitem>
-
- <listitem><para>
- <literal>table</literal>
- </para>
-
- <para>
- The table to which the row of output refers.
- </para></listitem>
-
- <listitem><para>
- <literal>type</literal>
- </para>
-
- <para>
- The join type. The different join types are listed here, ordered
- from the best type to the worst:
- </para>
-
- <itemizedlist>
-
- <listitem><para>
- <literal>system</literal>: The table has only one row (i.e. system
- table). This type will not often be encountered.
- </para></listitem>
-
- <listitem><para>
- <literal>const</literal>: The table has at most one matching row,
- which is read at the start of the query. Because there is only one
- row, values from the column in this row can be regarded as
- constants by optimizer. <literal>const</literal> tables are very
- fast because they are read only once.
- </para>
-
- <para>
- <literal>const</literal> is used when you compare all parts of a
- <literal>PRIMARY KEY</literal> or <literal>UNIQUE</literal> index
- with constant values. The tables in these queries would be classed
- as <literal>const</literal> because the columns in the where clause
- either are the primary key or form all parts of the primary key:
- </para>
-
- <para>
-<programlisting>
-SELECT * FROM <replaceable>film</replaceable> WHERE <replaceable>film_id</replaceable>=1
-
-SELECT * FROM <replaceable>film_actor</replaceable>
-WHERE <replaceable>film_id</replaceable>=1 AND <replaceable>actor_id</replaceable>=2
-</programlisting>
- </para></listitem>
-
- <listitem><para>
- <literal>eq_ref</literal>: One row is read from this table for each
- combination of rows from the previous tables. Other than the
- <literal>const</literal> types, this is the best possible join
- type. It is used when all parts of an index are used by the join
- and the index is a <literal>PRIMARY KEY</literal> or
- <literal>UNIQUE</literal> index.
- </para>
-
- <para>
- <literal>eq_ref</literal> can be used for indexed columns that are
- compared using the <literal>=</literal> operator. The comparison
- value can be a constant or an expression that uses columns from
- tables that are read before this table.
- </para>
-
- <para>
- In the following example, MySQL can use an
- <literal>eq_ref</literal> join to process the
- <literal>category</literal> table because the
- <literal>category_id</literal> column of the
- <literal>category</literal> table is the primary key and therefore
- there is only one matching row in the <literal>category</literal>
- table for each row in the <literal>film</literal> table:
- </para>
-
-<programlisting>
-SELECT film.title, category.name
-FROM film, catgeory
-WHERE film.category_id = category.category_id
-</programlisting></listitem>
-
- <listitem><para>
- <literal>ref</literal>: All rows with matching index values are
- read from this table for each combination of rows from the previous
- tables. <literal>ref</literal> is used if the join uses only part
- of a <literal>PRIMARY KEY</literal> or <literal>UNIQUE</literal>
- index of if the index does not enforce uniquness (in other words,
- if the join cannot select a single row based on the key value). If
- the key that is used matches only a few rows, this is a good join
- type.
- </para>
-
- <para>
- <literal>ref</literal> can be used for indexed columns that are
- compared using the <literal>=</literal> or
- <literal><=></literal> operator.
- </para>
-
- <para>
- In the following example, MySQL can use a <literal>ref</literal>
- join to process the <literal>inventory</literal> table because
- there is a non-unique index on the <literal>film_id</literal>
- column of the <literal>inventory</literal> table:
- </para>
-
- <para>
-<programlisting>
-SELECT film.title, inventory.inventory_id
-FROM film, inventory
-WHERE inventory.film_id = film.film_id
-</programlisting>
- </para></listitem>
-
- <listitem><para>
- <literal>ref_or_null</literal>: This join type is like
- <literal>ref</literal>, but with the addition that MySQL does an
- extra search for rows that contain <literal>NULL</literal> values.
- This join type is mostly used when resolving subqueries.
- </para>
-
- <para>
- In the following examples, MySQL can use a
- <literal>ref_or_null</literal> join to process
- <replaceable>ref_table</replaceable>:
- </para>
-
- <remark>
- [MH] UPDATEME FIND A QUERY FOR THE SAMPDB THAT CAUSES A ref_or_null
- </remark>
-
- <para>
-<programlisting>
-SELECT * FROM <replaceable>ref_table</replaceable>
-WHERE <replaceable>key_column</replaceable>=<replaceable>expr</replaceable> OR <replaceable>key_column</replaceable> IS NULL;
-</programlisting>
- </para></listitem>
-
- <listitem><para>
- <literal>index_merge</literal>: This join type indicates that an
- Index Merge optimization is used to reference multiple indexes. In
- this case, the <literal>key</literal> column contains a list of
- indexes used, and <literal>key_len</literal> contains a list of the
- longest key parts for the indexes used. For more information, see
- the
- <ulink url="http://dev.mysql.com/doc/mysql/en/index-merge-optimization.html">Index
- Merge Optimization</ulink> section of the MySQL Reference Manual.
- </para></listitem>
-
- <listitem><para>
- <literal>unique_subquery</literal>: This type replaces
- <literal>ref</literal> for some <literal>IN</literal> subqueries of
- the following form:
-
-<programlisting>
-<replaceable>value</replaceable> IN (SELECT <replaceable>primary_key</replaceable> FROM <replaceable>single_table</replaceable> WHERE <replaceable>some_expr</replaceable>)
-</programlisting>
-
- <literal>unique_subquery</literal> is just an index lookup function
- that replaces the subquery completely for better efficiency.
- </para></listitem>
-
- <listitem><para>
- <literal>index_subquery</literal>
- </para>
-
- <para>
- This join type is similar to <literal>unique_subquery</literal>. It
- replaces <literal>IN</literal> subqueries, but it works for
- non-unique indexes in subqueries of the following form:
- </para>
-
- <para>
-<programlisting>
-<replaceable>value</replaceable> IN (SELECT <replaceable>key_column</replaceable> FROM <replaceable>single_table</replaceable> WHERE <replaceable>some_expr</replaceable>)
-</programlisting>
- </para></listitem>
-
- <listitem><para>
- <literal>range</literal>: Only rows that are in a given range are
- retrieved, using an index to select the rows. The
- <literal>key</literal> column indicates which index is used. The
- <literal>key_len</literal> contains the longest key part that was
- used. The <literal>ref</literal> column is <literal>NULL</literal>
- for this type.
- </para>
-
- <para>
- <literal>range</literal> can be used for when a key column is
- compared to a constant using any of the <literal>=</literal>,
- <literal><></literal>, <literal>></literal>,
- <literal>>=</literal>, <literal><</literal>,
- <literal><=</literal>, <literal>IS NULL</literal>,
- <literal><=></literal>, <literal>BETWEEN</literal>, or
- <literal>IN</literal> operators:
- </para>
-
- <para>
-<programlisting>
-SELECT * FROM <replaceable>tbl_name</replaceable>
-WHERE <replaceable>key_column</replaceable> = 10;
-
-SELECT * FROM <replaceable>tbl_name</replaceable>
-WHERE <replaceable>key_column</replaceable> BETWEEN 10 and 20;
-
-SELECT * FROM <replaceable>tbl_name</replaceable>
-WHERE <replaceable>key_column</replaceable> IN (10,20,30);
-
-SELECT * FROM <replaceable>tbl_name</replaceable>
-WHERE <replaceable>key_part1</replaceable>= 10 AND <replaceable>key_part2</replaceable> IN (10,20,30);
-</programlisting>
- </para></listitem>
-
- <listitem><para>
- <literal>index</literal>: This join type is the same as
- <literal>ALL</literal>, except that only the index tree is scanned.
- This usually is faster than <literal>ALL</literal>, because the
- index file usually is smaller than the data file.
- </para>
-
- <para>
- MySQL can use this join type when the query uses only columns that
- are part of a single index.
- </para></listitem>
-
- <listitem><para>
- <literal>ALL</literal>: A full table scan is done for each
- combination of rows from the previous tables. This is normally not
- good if the table is the first table not marked
- <literal>const</literal>, and usually <emphasis>very</emphasis> bad
- in all other cases. Normally, you can avoid <literal>ALL</literal>
- by adding indexes that allow row retrieval from the table based on
- constant values or column values from earlier tables.
- </para></listitem>
-
- </itemizedlist></listitem>
-
- <listitem><para>
- <literal>possible_keys</literal>
- </para>
-
- <para>
- The <literal>possible_keys</literal> column indicates which indexes
- MySQL could use to find the rows in this table. This column is
- totally independent of the order of the tables as displayed in the
- output from <literal>EXPLAIN</literal>. That means that some of the
- keys in <literal>possible_keys</literal> might not be usable in
- practice with the generated table order.
- </para>
-
- <para>
- If this column is <literal>NULL</literal>, there are no relevant
- indexes. In this case, you may be able to improve the performance of
- your query by examining the <literal>WHERE</literal> clause to see
- whether it refers to some column or columns that would be suitable
- for indexing. If so, create an appropriate index and check the query
- with <literal>EXPLAIN</literal> again.
- </para></listitem>
-
- <listitem><para>
- <literal>key</literal>
- </para>
-
- <para>
- The <literal>key</literal> column indicates the key (index) that
- MySQL actually decided to use. The key is <literal>NULL</literal> if
- no index was chosen.
- </para>
-
- <para>
- For <literal>MyISAM</literal> tables, running <literal>ANALYZE
- TABLE</literal> helps the optimizer choose better indexes.
- </para></listitem>
-
- <listitem><para>
- <literal>key_len</literal>
- </para>
-
- <para>
- The <literal>key_len</literal> column indicates the length of the
- key that MySQL decided to use. The value of
- <literal>key_len</literal> allows you to determine how many parts of
- a multiple-part key MySQL actually uses.
- </para></listitem>
-
- <listitem><para>
- <literal>ref</literal>
- </para>
-
- <para>
- The <literal>ref</literal> column shows which columns or constants
- are used with the <literal>key</literal> to select rows from the
- table.
- </para></listitem>
-
- <listitem><para>
- <literal>rows</literal>
- </para>
-
- <para>
- The <literal>rows</literal> column indicates the number of rows
- MySQL believes it must examine to execute the query.
- </para></listitem>
-
- <listitem><para>
- <literal>Extra</literal>
- </para>
-
- <para>
- This column contains additional information about how MySQL resolves
- the query. Here is an explanation of the different text strings that
- can appear in this column:
- </para>
-
- <itemizedlist>
-
- <listitem><para>
- <literal>Distinct</literal>: MySQL stops searching for more rows
- for the current row combination after it has found the first
- matching row.
- </para></listitem>
-
- <listitem><para>
- <literal>Not exists</literal>: MySQL was able to do a <literal>LEFT
- JOIN</literal> optimization on the query and does not examine more
- rows in this table for the previous row combination after it finds
- one row that matches the <literal>LEFT JOIN</literal> criteria.
- </para>
-
- <para>
- Here is an example of the type of query that can be optimized this
- way:
- </para>
-
- <para>
-<programlisting>
-SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id
-WHERE t2.id IS NULL;
-</programlisting>
- </para>
-
- <para>
- Assume that <literal>t2.id</literal> is defined as <literal>NOT
- NULL</literal>. In this case, MySQL scans <literal>t1</literal> and
- looks up the rows in <literal>t2</literal> using the values of
- <literal>t1.id</literal>. If MySQL finds a matching row in
- <literal>t2</literal>, it knows that <literal>t2.id</literal> can
- never be <literal>NULL</literal>, and does not scan through the
- rest of the rows in <literal>t2</literal> that have the same
- <literal>id</literal> value. In other words, for each row in
- <literal>t1</literal>, MySQL needs to do only a single lookup in
- <literal>t2</literal>, regardless of how many rows actually match
- in <literal>t2</literal>.
- </para></listitem>
-
- <listitem><para>
- <literal>range checked for each record (index map: #)</literal>:
- MySQL found no good index to use, but found that some of indexes
- might be used once 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
- <literal>index_merge</literal> access method to retrieve rows.
- </para>
-
- <para>
- This is not very fast, but is faster than performing a join with no
- index at all.
- </para></listitem>
-
- <listitem><para>
- <literal>Using filesort</literal>: MySQL needs to do an extra pass
- to find out how to retrieve the rows in sorted order. The sort is
- done by going through all rows according to the join type and
- storing the sort key and pointer to the row for all rows that match
- the <literal>WHERE</literal> clause. The keys then are sorted and
- the rows are retrieved in sorted order.
- </para></listitem>
-
- <listitem><para>
- <literal>Using index</literal>: The column information is retrieved
- from the table using only information in the index tree without
- having to do an additional seek to read the actual row. This
- strategy can be used when the query uses only columns that are part
- of a single index.
- </para></listitem>
-
- <listitem><para>
- <literal>Using temporary</literal>: To resolve the query, MySQL
- needs to create a temporary table to hold the result. This
- typically happens if the query contains <literal>GROUP BY</literal>
- and <literal>ORDER BY</literal> clauses that list columns
- differently.
- </para></listitem>
-
- <listitem><para>
- <literal>Using where</literal>: A <literal>WHERE</literal> clause
- is used to restrict which rows to match against the next table or
- send to the client. Unless you specifically intend to fetch or
- examine all rows from the table, you may have something wrong in
- your query if the <literal>Extra</literal> value is not
- <literal>Using where</literal> and the table join type is
- <literal>ALL</literal> or <literal>index</literal>.
- </para>
-
- <para>
- If you want to make your queries as fast as possible, you should
- look out for <literal>Extra</literal> values of <literal>Using
- filesort</literal> and <literal>Using temporary</literal>.
- </para></listitem>
-
- <listitem><para>
- <literal>Using sort_union(...)</literal> , <literal>Using
- union(...)</literal> , <literal>Using intersect(...)</literal>:
- These indicate how index scans are merged for the
- <literal>index_merge</literal> join type.
- </para></listitem>
-
- <listitem><para>
- <literal>Using index for group-by</literal>: Similar to the
- <literal>Using index</literal> way of accessing a table,
- <literal>Using index for group-by</literal> indicates that MySQL
- found an index that can be used to retrieve all columns of a
- <literal>GROUP BY</literal> or <literal>DISTINCT</literal> query
- without any extra disk access to the actual table. Additionally,
- the index is used in the most efficient way so that for each group,
- only a few index entries are read.
- </para></listitem>
-
- </itemizedlist></listitem>
-
- </itemizedlist>
-
- <remark>
- [MH] NEED TO ADD AN EXAMPLE OF OPTIMIZING A QUERY, FROM THE SAMPLE DB
- IF POSSIBLE.
- </remark>
-
<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 Manial.
+ section of the MySQL Reference Manual.
</para>
</section>
--- 1.4/userguide/userguide.xml 2005-06-25 12:51:54 -06:00
+++ 1.5/userguide/userguide.xml 2005-06-28 15:06:26 -06:00
@@ -3,11 +3,15 @@
"http://www.oasis-open.org/docbook/xml/4.3/docbookx.dtd">
<book id="userguide" lang="en">
- <title>MySQL User Guide</title>
+ <bookinfo>
+
+ <title>MySQL User Guide</title>
+
+ </bookinfo>
<xi:include href="introduction.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
- <part label="Part I: MySQL Usage">
+ <part label="Part I">
<title>Using MySQL</title>
@@ -18,9 +22,10 @@
<xi:include href="querying.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
<xi:include href="modifying.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
-</part>
-<part label="Part II: Advanced MySQL Usage">
+ </part>
+
+ <part label="Part II">
<title>Advanced MySQL Usage</title>
@@ -34,7 +39,7 @@
</part>
-<part label="Part III: MySQL Administration">
+ <part label="Part III">
<title>MySQL Server Administration</title>
@@ -44,13 +49,13 @@
<xi:include href="security.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
-<xi:include href="logfiles.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
+ <xi:include href="logfiles.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
- <xi:include href="disaster.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
+ <xi:include href="disaster.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
</part>
- <part label="Part IIII: MySQL APIs">
+ <part label="Part IV">
<title>Using the MySQL APIs</title>
@@ -59,6 +64,14 @@
<xi:include href="dotnet.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
<xi:include href="java.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
+
+ </part>
+
+ <part label="Part V">
+
+ <title>Appendices</title>
+
+ <xi:include href="glossary.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
</part>
| Thread |
|---|
| • bk commit - mysqldoc@docsrva tree (Mike.Hillyer:1.2885) | mhillyer | 28 Jun |
