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.2773 05/06/15 16:05:49 Mike.Hillyer@stripped +6 -0
Minor manual update, some userguide work.
userguide/images/indexing-displaying-administrator.png
1.1 05/06/15 16:05:48 Mike.Hillyer@stripped +1488 -0
userguide/images/indexing-choosing-slowlog-administrator-view.png
1.1 05/06/15 16:05:48 Mike.Hillyer@stripped +481 -0
userguide/images/indexing-choosing-slowlog-administrator-settings.png
1.1 05/06/15 16:05:48 Mike.Hillyer@stripped +1263 -0
userguide/userguide.xml
1.3 05/06/15 16:05:48 Mike.Hillyer@stripped +21 -16
Changed splitting of chapters.
userguide/indexing.xml
1.2 05/06/15 16:05:48 Mike.Hillyer@stripped +456 -6
Work in progress commit on indexing chapter.
userguide/images/indexing-displaying-administrator.png
1.0 05/06/15 16:05:48 Mike.Hillyer@stripped +0 -0
BitKeeper file /home/mysqldoc/mysqldoc/userguide/images/indexing-displaying-administrator.png
userguide/images/indexing-choosing-slowlog-administrator-view.png
1.0 05/06/15 16:05:48 Mike.Hillyer@stripped +0 -0
BitKeeper file /home/mysqldoc/mysqldoc/userguide/images/indexing-choosing-slowlog-administrator-view.png
userguide/images/indexing-choosing-slowlog-administrator-settings.png
1.0 05/06/15 16:05:48 Mike.Hillyer@stripped +0 -0
BitKeeper file /home/mysqldoc/mysqldoc/userguide/images/indexing-choosing-slowlog-administrator-settings.png
Docs/manual.texi
1.2990 05/06/15 16:05:46 Mike.Hillyer@stripped +7 -3
Edited wording as per PGulutzan's request.
# 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
--- New file ---
+++ userguide/images/indexing-choosing-slowlog-administrator-settings.png 05/06/15 16:05:48
tEXtCopyright
dtTʻ
<
W]
--- New file ---
+++ userguide/images/indexing-choosing-slowlog-administrator-view.png 05/06/15 16:05:48
tEXtCopyright
C&
@&
ǘ
--- New file ---
+++ userguide/images/indexing-displaying-administrator.png 05/06/15 16:05:48
tEXtCopyright
"
U
I*p>
c
I*p>
MS
--- 1.1/userguide/indexing.xml 2005-06-08 15:44:32 -06:00
+++ 1.2/userguide/indexing.xml 2005-06-15 16:05:48 -06:00
@@ -5,31 +5,471 @@
<title>Indexing Data</title>
- <section id="indexing-why">
+ <section id="indexing-introduction">
- <title>Why Use Indexes?</title>
+ <title>Introduction to Indexing</title>
- <para></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 a
+ index in a book: books are not (typically) organized by alphabetical
+ 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>
+ When you find yourself in such a situation you look at the index. In
+ the index, the contents of the book are displayed 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, the 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 and 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, MySQL 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>
+ 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.
+ </para>
</section>
-<!-- END WHY -->
+<!-- END INTRODUCTION -->
<section id="indexing-types">
<title>Types of Indexes</title>
- <para></para>
+ <para>
+ There are five different index types that you will typically 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 information in <literal>CHAR</literal>,
+ <literal>VARCHAR</literal>, and <literal>TEXT</literal> columns. The
+ <literal>FULLTEXT</literal> index is available for the
+ <literal>MyISAM</literal> storage engine 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>.
+ </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>.
+ </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>
</section>
<!-- END TYPES -->
+ <section id="indexing-choosing">
+
+ <title>Choosing Columns to Index</title>
+
+ <para>
+ To use indexes effectively, it is necessary to identify those queries
+ that are executing slowly and not making effective use of indexes.
+ This can either be done 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>
+SELECT last_name, first_name FROM actor WHERE last_name = 'Brando'
+</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>
+
+<programlisting>
+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
+</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>
+ Remember that 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>
+
+<!-- END REVIEWING -->
+
+ <section id="indexing-choosing-slowlog">
+
+ <title>Identifying Slow Queries with the Slow Query Log</title>
+
+ <para></para>
+
+ <figure>
+ <title>Activating the Slow Query Log using MySQL Administrator</title>
+ <graphic fileref="images/indexing-choosing-slowlog-administrator-settings.png" lang="en" format="PNG"/>
+ </figure>
+
+ <figure>
+ <title>Viewing the Slow Query Log with MySQL Administrator</title>
+ <graphic fileref="images/indexing-choosing-slowlog-administrator-view.png" lang="en" format="PNG"/>
+ </figure>
+
+ </section>
+
+<!-- END SLOW LOG -->
+
+ </section>
+
+<!-- END CHOOSING -->
+
<section id="indexing-displaying">
<title>Displaying Table Indexes</title>
- <para></para>
+ <para>
+ Before creating a new index, it is important to know what indexes
+ currently exist for a given table. Index information can be retreived
+ using either the <literal>SHOW</literal> syntax or through use of the
+ <literal>INFORMATION_SCHEMA</literal>. Index information can also be
+ browsed using the MySQL GUI tools.
+ </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 active
+ schema to display the index information for a table:
+ </para>
+
+<programlisting>
+mysql> USE sakila;
+Database changed
+
+mysql> SHOW INDEX FROM film\G
+*************************** 1. row ***************************
+ Table: film
+ Non_unique: 0
+ Key_name: PRIMARY
+Seq_in_index: 1
+ Column_name: film_id
+ Collation: A
+ Cardinality: 2
+ Sub_part: NULL
+ Packed: NULL
+ Null:
+ Index_type: BTREE
+ Comment:
+*************************** 2. row ***************************
+ Table: film
+ Non_unique: 1
+ Key_name: Title_Description_Fulltext
+Seq_in_index: 1
+ Column_name: title
+ Collation: NULL
+ Cardinality: NULL
+ Sub_part: NULL
+ Packed: NULL
+ Null:
+ Index_type: FULLTEXT
+ Comment:
+*************************** 3. row ***************************
+ Table: film
+ Non_unique: 1
+ Key_name: Title_Description_Fulltext
+Seq_in_index: 2
+ Column_name: description
+ Collation: NULL
+ Cardinality: NULL
+ Sub_part: NULL
+ Packed: NULL
+ Null: YES
+ Index_type: FULLTEXT
+ Comment:
+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>
+
+ </section>
+
+<!-- END SHOW -->
+
+ <section id="indexing-displaying-information-schema">
+
+ <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>
+
+<programlisting>
+mysql> SELECT * FROM INFORMATION_SCHEMA.STATISTICS
+ -> WHERE TABLE_SCHEMA = 'sakila'
+ -> AND table_name = 'film'\G
+*************************** 1. row ***************************
+TABLE_CATALOG: NULL
+ TABLE_SCHEMA: sakila
+ TABLE_NAME: film
+ NON_UNIQUE: 0
+ INDEX_SCHEMA: sakila
+ INDEX_NAME: PRIMARY
+ SEQ_IN_INDEX: 1
+ COLUMN_NAME: film_id
+ COLLATION: A
+ CARDINALITY: 2
+ SUB_PART: NULL
+ PACKED: NULL
+ NULLABLE:
+ INDEX_TYPE: BTREE
+ COMMENT:
+*************************** 2. row ***************************
+TABLE_CATALOG: NULL
+ TABLE_SCHEMA: sakila
+ TABLE_NAME: film
+ NON_UNIQUE: 1
+ INDEX_SCHEMA: sakila
+ INDEX_NAME: Title_Description_Fulltext
+ SEQ_IN_INDEX: 1
+ COLUMN_NAME: title
+ COLLATION: NULL
+ CARDINALITY: NULL
+ SUB_PART: NULL
+ PACKED: NULL
+ NULLABLE:
+ INDEX_TYPE: FULLTEXT
+ COMMENT:
+*************************** 3. row ***************************
+TABLE_CATALOG: NULL
+ TABLE_SCHEMA: sakila
+ TABLE_NAME: film
+ NON_UNIQUE: 1
+ INDEX_SCHEMA: sakila
+ INDEX_NAME: Title_Description_Fulltext
+ SEQ_IN_INDEX: 2
+ COLUMN_NAME: description
+ COLLATION: NULL
+ CARDINALITY: NULL
+ SUB_PART: NULL
+ PACKED: NULL
+ NULLABLE: YES
+ INDEX_TYPE: FULLTEXT
+ COMMENT:
+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>
+
+ </section>
+
+<!-- END INFO SCHEMA -->
+
+ <section id="indexing-displaying-administrator">
+
+ <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>
+
+ </section>
+
+<!-- END ADMINISTRATOR -->
</section>
@@ -44,6 +484,16 @@
</section>
<!-- END CREATING -->
+
+ <section id="indexing-fulltext">
+
+ <title>Using a FULLTEXT Index</title>
+
+ <para></para>
+
+ </section>
+
+<!-- END FULLTEXT -->
<section id="indexing-dropping">
--- 1.2/userguide/userguide.xml 2005-06-13 14:56:27 -06:00
+++ 1.3/userguide/userguide.xml 2005-06-15 16:05:48 -06:00
@@ -7,21 +7,7 @@
<xi:include href="introduction.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
- <part label="Part I: MySQL Administration">
-
- <title>MySQL Server Administration</title>
-
- <xi:include href="installing.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
-
- <xi:include href="configuring.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
-
- <xi:include href="security.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 II: MySQL Usage">
+ <part label="Part I: MySQL Usage">
<title>Using MySQL</title>
@@ -32,6 +18,11 @@
<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">
+
+ <title>Advanced MySQL Usage</title>
<xi:include href="indexing.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
@@ -43,7 +34,21 @@
</part>
- <part label="Part III: MySQL APIs">
+<part label="Part III: MySQL Administration">
+
+ <title>MySQL Server Administration</title>
+
+ <xi:include href="installing.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
+
+ <xi:include href="configuring.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
+
+ <xi:include href="security.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">
<title>Using the MySQL APIs</title>
--- 1.2989/Docs/manual.texi 2005-06-13 15:04:15 -06:00
+++ 1.2990/Docs/manual.texi 2005-06-15 16:05:46 -06:00
@@ -6517,6 +6517,10 @@
and use @code{glibc}.
To obtain RPM packages, see @ref{Getting MySQL}.
+MySQL AB does provide some platform-specific RPMs; the difference between a platform-specific
+RPM and a generic RPM is that the platform-specific RPMs are built on the targeted platform
+and are linked dynamically whereas the generic RPM is linked statically with LinuxThreads.
+
@strong{Note}: RPM distributions of MySQL often are provided by other
vendors. Be aware that they may differ in features and capabilities from
those built by MySQL AB, and that the instructions in this manual do not
@@ -45028,9 +45032,9 @@
@ss{} = ss
@end example
-Language-specific collations for the @code{utf8} character set are
-implemented only if the default order provided by @code{utf8_unicode_ci}
-does not work for a language well. For example, @code{utf8_unicode_ci}
+Language-specific collations for the utf8 character set are implemented
+only if the ordering with utf8_unicode_ci does not work well for
+a language. For example, @code{utf8_unicode_ci}
works fine for German and French, so there is no need to create special
@code{utf8} collations for these two languages.
| Thread |
|---|
| • bk commit - mysqldoc@docsrva tree (Mike.Hillyer:1.2773) | mhillyer | 16 Jun |
