Below is the list of changes that have just been committed into a local
mysqldoc repository of jon. When jon 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.3111 05/07/24 01:09:56 jon@stripped +1 -0
More RefMan-5.0 edits.
refman-5.0/mysql-optimization.xml
1.6 05/07/24 01:09:55 jon@stripped +141 -153
More RefMan-5.0 edits.
# 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: jon
# Host: gigan.
# Root: /home/jon/bk/mysqldoc
--- 1.5/refman-5.0/mysql-optimization.xml 2005-07-23 23:43:06 +10:00
+++ 1.6/refman-5.0/mysql-optimization.xml 2005-07-24 01:09:55 +10:00
@@ -4771,7 +4771,7 @@
</listitem>
<!--
[JS] True, but do we really need to say so?
- This sounds just a bit defencive...
+ This sounds just a bit defensive...
-->
<listitem>
<para>
@@ -4788,7 +4788,7 @@
<listitem>
<para>
- Replication can provide a performance benefit for some
+ Replication can provide performance benefits for some
operations. You can distribute client retrievals among
replication servers to split up the load. To avoid slowing
down the master while making backups, you can make backups
@@ -5129,26 +5129,27 @@
<listitem>
<para>
- Versioning (such as we use in MySQL for concurrent
- inserts) where you can have one writer at the same time as
- many readers. This means that the database/table supports
- different views for the data depending on when you started
- to access it. Other names for this are time travel, copy
- on write, or copy on demand.
+ Versioning (such as that used in MySQL for concurrent
+ inserts) where it is possible to have one writer at the
+ same time as many readers. This means that the database or
+ table supports different views for the data depending on
+ when access begins. Other common terms for this are
+ <quote>time travel</quote>, <quote>copy on write</quote>,
+ or <quote>copy on demand</quote>.
</para>
</listitem>
<listitem>
<para>
- Copy on demand is in many cases much better than
- page-level or row-level locking. However, the worst case
- does use much more memory than when using normal locks.
+ Copy on demand is in many cases superior to
+ page-level or row-level locking. However, in the worst
+ case, it can use much more memory than using normal locks.
</para>
</listitem>
<listitem>
<para>
- Instead of using row-level locks, you can use
+ Instead of using row-level locks, you can employ
application-level locks, such as
<literal>GET_LOCK()</literal> and
<literal>RELEASE_LOCK()</literal> in MySQL. These are
@@ -5426,7 +5427,10 @@
<indexterm type="concept">
<primary>storage of data</primary>
</indexterm>
-
+<!--
+ TODO: Apparently much of the info in this section is outdated or
+ Just Plain Wrong. This should be fixed ASAP.
+-->
<para>
MySQL keeps row data and index data in separate files. Many
(almost all) other databases mix row and index data in the same
@@ -5556,7 +5560,8 @@
<para>
Use the smaller integer types if possible to get smaller
tables. For example, <literal>MEDIUMINT</literal> is often
- better than <literal>INT</literal>.
+ a better choice than <literal>INT</literal> since a
+ <literal>MEDIUMINT</literal> column uses 25% less space.
</para>
</listitem>
@@ -5572,7 +5577,7 @@
<listitem>
<para>
- For <literal>MyISAM</literal> tables, if you don't have any
+ For <literal>MyISAM</literal> tables, if you do not have any
variable-length columns (<literal>VARCHAR</literal>,
<literal>TEXT</literal>, or <literal>BLOB</literal>
columns), a fixed-size record format is used. This is faster
@@ -5628,20 +5633,20 @@
<listitem>
<para>
Create only the indexes that you really need. Indexes are
- good for retrieval but bad when you need to store things
- fast. If you mostly access a table by searching on a
+ good for retrieval but bad when you need to store data
+ quickly. If you access a table mostly by searching on a
combination of columns, make an index on them. The first
- index part should be the most used column. If you are
- <emphasis>always</emphasis> using many columns, you should
- use the column with more duplicates first to get better
- compression of the index.
+ index part should be the most used column. If you
+ <emphasis>always</emphasis> use many columns when selecting
+ from the table, you should use the column with more
+ duplicates first to obtain better compression of the index.
</para>
</listitem>
<listitem>
<para>
- If it's very likely that a column has a unique prefix on the
- first number of characters, it's better to index only this
+ If it is very likely that a column has a unique prefix on the
+ first number of characters, it is better to index only this
prefix. MySQL supports an index on the leftmost part of a
character column. Shorter indexes are faster not only
because they take less disk space, but also because they
@@ -5727,12 +5732,12 @@
</indexterm>
<para>
- The <literal>MyISAM</literal> and (as of MySQL 4.0.14)
- <literal>InnoDB</literal> storage engines also support indexing
- on <literal>BLOB</literal> and <literal>TEXT</literal> columns.
- When indexing a <literal>BLOB</literal> or
- <literal>TEXT</literal> column, you <emphasis>must</emphasis>
- specify a prefix length for the index. For example:
+ The <literal>MyISAM</literal> and <literal>InnoDB</literal>
+ storage engines also support indexing on <literal>BLOB</literal>
+ and <literal>TEXT</literal> columns. When indexing a
+ <literal>BLOB</literal> or <literal>TEXT</literal> column, you
+ <emphasis>must</emphasis> specify a prefix length for the index.
+ For example:
</para>
<programlisting>
@@ -5740,37 +5745,37 @@
</programlisting>
<para>
- Prefixes can be up to 255 bytes long (or 1000 bytes for
- <literal>MyISAM</literal> and <literal>InnoDB</literal> tables
- as of MySQL 4.1.2). Note that prefix limits are measured in
- bytes, whereas the prefix length in <literal>CREATE
- TABLE</literal> statements is interpreted as number of
- characters. Take this into account when specifying a prefix
- length for a column that uses a multi-byte character set.
+ Im MySQL 5.0, prefixes can be up to 1000 bytes long for
+ <literal>MyISAM</literal> and <literal>InnoDB</literal> tables.
+ Note that prefix limits are measured in bytes, whereas the
+ prefix length in <literal>CREATE TABLE</literal> statements is
+ interpreted as number of characters. <emphasis>Be sure to take
+ this into account when specifying a prefix length for a column
+ that uses a multi-byte character set</emphasis>.
</para>
<para>
- As of MySQL 3.23.23, you can also create
- <literal>FULLTEXT</literal> indexes. They are used for full-text
- searches. Only the <literal>MyISAM</literal> table type supports
+ You can also create <literal>FULLTEXT</literal> indexes. These
+ are used for full-text searches. In MySQL 5.0, only the
+ <literal>MyISAM</literal> storage engine supports
<literal>FULLTEXT</literal> indexes and only for
<literal>CHAR</literal>, <literal>VARCHAR</literal>, and
- <literal>TEXT</literal> columns. Indexing always happens over
- the entire column and partial (prefix) indexing is not
+ <literal>TEXT</literal> columns. Indexing always takes place
+ over the entire column and partial (prefix) indexing is not
supported. See <xref linkend="fulltext-search"/> for details.
</para>
<para>
- As of MySQL 4.1.0, you can create indexes on spatial column
- types. Currently, spatial types are supported only by the
+ In MySQL 5.0, you can also create indexes on spatial column
+ types. Spatial types are supported only by the
<literal>MyISAM</literal> storage engine. Spatial indexes use
R-trees.
</para>
<para>
The <literal>MEMORY</literal> (<literal>HEAP</literal>) storage
- engine uses hash indexes by default. It also supports B-tree
- indexes as of MySQL 4.1.0.
+ engine uses hash indexes by default, but also supports B-tree
+ indexes in MySQL 5.0.
</para>
</section>
@@ -5818,11 +5823,12 @@
<programlisting>
CREATE TABLE test (
- id INT NOT NULL,
- last_name CHAR(30) NOT NULL,
- first_name CHAR(30) NOT NULL,
- PRIMARY KEY (id),
- INDEX name (last_name,first_name));
+ id INT NOT NULL,
+ last_name CHAR(30) NOT NULL,
+ first_name CHAR(30) NOT NULL,
+ PRIMARY KEY (id),
+ INDEX name (last_name,first_name)
+);
</programlisting>
<para>
@@ -5839,15 +5845,15 @@
SELECT * FROM test WHERE last_name='Widenius';
SELECT * FROM test
- WHERE last_name='Widenius' AND first_name='Michael';
+ WHERE last_name='Widenius' AND first_name='Michael';
SELECT * FROM test
- WHERE last_name='Widenius'
- AND (first_name='Michael' OR first_name='Monty');
+ WHERE last_name='Widenius'
+ AND (first_name='Michael' OR first_name='Monty');
SELECT * FROM test
- WHERE last_name='Widenius'
- AND first_name >='M' AND first_name < 'N';
+ WHERE last_name='Widenius'
+ AND first_name >='M' AND first_name < 'N';
</programlisting>
<para>
@@ -5859,7 +5865,7 @@
SELECT * FROM test WHERE first_name='Michael';
SELECT * FROM test
- WHERE last_name='Widenius' OR first_name='Michael';
+ WHERE last_name='Widenius' OR first_name='Michael';
</programlisting>
<para>
@@ -5879,25 +5885,25 @@
</indexterm>
<para>
- Indexes are used to find rows with specific column values fast.
- Without an index, MySQL has to start with the first record and
- then read through the whole table to find the relevant rows. The
- larger the table, the more this costs. If the table has an index
- for the columns in question, MySQL can quickly determine the
- position to seek to in the middle of the data file without
- having to look at all the data. If a table has 1,000 rows, this
- is at least 100 times faster than reading sequentially. Note
- that if you need to access almost all 1,000 rows, it is faster
- to read sequentially, because that minimizes disk seeks.
+ Indexes are used to find rows with specific column values
+ quickly. Without an index, MySQL must begin with the first
+ record and then read through the entire table to find the
+ relevant rows. The larger the table, the more this costs. If the
+ table has an index for the columns in question, MySQL can
+ quickly determine the position to seek to in the middle of the
+ data file without having to look at all the data. If a table has
+ 1,000 rows, then this is at least 100 times faster than reading
+ sequentially. Note that if you need to access most of the rows,
+ it is faster to read sequentially, because this minimizes disk
+ seeks.
</para>
<para>
Most MySQL indexes (<literal>PRIMARY KEY</literal>,
<literal>UNIQUE</literal>, <literal>INDEX</literal>, and
<literal>FULLTEXT</literal>) are stored in B-trees. Exceptions
- are that indexes on spatial column types use R-trees, and
- <literal>MEMORY</literal> (<literal>HEAP</literal>) tables
- support hash indexes.
+ are that indexes on spatial column types use R-trees, and that
+ <literal>MEMORY</literal> tables also support hash indexes.
</para>
<para>
@@ -5920,8 +5926,8 @@
<listitem>
<para>
- To quickly find the rows that match a
- <literal>WHERE</literal> clause.
+ To find the rows matching a <literal>WHERE</literal> clause
+ quickly.
</para>
</listitem>
@@ -5957,7 +5963,7 @@
<programlisting>
SELECT MIN(<replaceable>key_part2</replaceable>),MAX(<replaceable>key_part2</replaceable>)
-FROM <replaceable>tbl_name</replaceable> WHERE <replaceable>key_part1</replaceable>=10;
+ FROM <replaceable>tbl_name</replaceable> WHERE <replaceable>key_part1</replaceable>=10;
</programlisting>
</listitem>
@@ -5983,7 +5989,8 @@
</para>
<programlisting>
-SELECT <replaceable>key_part3</replaceable> FROM <replaceable>tbl_name</replaceable> WHERE <replaceable>key_part1</replaceable>=1
+SELECT <replaceable>key_part3</replaceable> FROM <replaceable>tbl_name</replaceable>
+ WHERE <replaceable>key_part1</replaceable>=1
</programlisting>
</listitem>
@@ -6027,7 +6034,7 @@
</para>
<para>
- MySQL can't use a partial index if the columns don't form a
+ MySQL cannot use a partial index if the columns do not form a
leftmost prefix of the index. Suppose that you have the
<literal>SELECT</literal> statements shown here:
</para>
@@ -6093,8 +6100,8 @@
</para>
<para>
- The following <literal>SELECT</literal> statements do not use
- indexes:
+ The following <literal>SELECT</literal> statements do
+ <emphasis>not</emphasis> use indexes:
<programlisting>
SELECT * FROM <replaceable>tbl_name</replaceable> WHERE <replaceable>key_col</replaceable> LIKE '%Patrick%';
@@ -6109,13 +6116,14 @@
</para>
<para>
- MySQL 4.0 and up performs an additional <literal>LIKE</literal>
+ MySQL 5.0 performs an additional <literal>LIKE</literal>
optimization. If you use <literal>... LIKE
'%<replaceable>string</replaceable>%'</literal> and
<replaceable>string</replaceable> is longer than three
- characters, MySQL uses the <literal>Turbo Boyer-Moore</literal>
- algorithm to initialize the pattern for the string and then use
- this pattern to perform the search quicker.
+ characters, MySQL uses the <firstterm>Turbo Boyer-Moore
+ algorithm</firstterm> to initialize the pattern for the string
+ and then employs this pattern to perform the search more
+ quickly.
</para>
<indexterm type="function">
@@ -6129,13 +6137,13 @@
</indexterm>
<para>
- Searching using <literal><replaceable>col_name</replaceable> IS
- NULL</literal> uses indexes if
+ A search using <literal><replaceable>col_name</replaceable> IS
+ NULL</literal> employs indexes if
<replaceable>col_name</replaceable> is indexed.
</para>
<para>
- Any index that doesn't span all <literal>AND</literal> levels in
+ Any index that does not span all <literal>AND</literal> levels in
the <literal>WHERE</literal> clause is not used to optimize the
query. In other words, to be able to use an index, a prefix of
the index must be used in every <literal>AND</literal> group.
@@ -6160,9 +6168,10 @@
<emphasis>not</emphasis> use indexes:
</para>
-<!-- NOTE: the comments for the 2nd query below does not seem to make sense. -->
-
-<!-- The comment for the 3rd query does not seem correct. -->
+<!--
+ NOTE: the comment for the 2nd query below does not seem to make
+ sense. The comment for the 3rd query does not seem correct.
+-->
<programlisting>
/* <replaceable>index_part1</replaceable> is not used */
@@ -6175,13 +6184,14 @@
<para>
Sometimes MySQL does not use an index, even if one is available.
- One way this occurs is when the optimizer estimates that using
- the index would require MySQL to access a large percentage of
- the rows in the table. (In this case, a table scan is probably
- much faster, because it requires fewer seeks.) However, if such
- a query uses <literal>LIMIT</literal> to only retrieve part of
- the rows, MySQL uses an index anyway, because it can much more
- quickly find the few rows to return in the result.
+ One circumstance under which this occurs is when the optimizer
+ estimates that using the index would require MySQL to access a
+ very large percentage of the rows in the table. (In this case, a
+ table scan is likely to be much faster, since it requires fewer
+ seeks.) However, if such a query uses <literal>LIMIT</literal>
+ to only retrieve some of the rows, MySQL uses an index anyway,
+ because it can much more quickly find the few rows to return in
+ the result.
</para>
<para>
@@ -6255,10 +6265,10 @@
<listitem>
<para>
- For index blocks, a special structure called the key cache
- (key buffer) is maintained. The structure contains a number
- of block buffers where the most-used index blocks are
- placed.
+ For index blocks, a special structure called the
+ <firstterm>key cache</firstterm> (key buffer) is maintained.
+ The structure contains a number of block buffers where the
+ most-used index blocks are placed.
</para>
</listitem>
@@ -6273,9 +6283,9 @@
<para>
This section first describes the basic operation of the
- <literal>MyISAM</literal> key cache. Then it discusses changes
- made in MySQL 4.1 that improve key cache performance and that
- enable you to better control cache operation:
+ <literal>MyISAM</literal> key cache. Then it discusses recent
+ changes (made in MySQL 4.1) that improve key cache performance
+ and that enable you to better control cache operation:
</para>
<itemizedlist>
@@ -6297,15 +6307,6 @@
</itemizedlist>
<para>
- The key cache mechanism also is used for <literal>ISAM</literal>
- tables. However, the significance of this fact is on the wane.
- <literal>ISAM</literal> table use has been decreasing since
- MySQL 3.23 when <literal>MyISAM</literal> was introduced. MySQL
- 4.1 carries this trend further; the <literal>ISAM</literal>
- storage engine is disabled by default.
- </para>
-
- <para>
You can control the size of the key cache by means of the
<literal>key_buffer_size</literal> system variable. If this
variable is set equal to zero, no key cache is used. The key
@@ -6352,20 +6353,21 @@
<para>
If it happens that a block selected for replacement has been
- modified, the block is considered ``dirty.'' In this case,
- before being replaced, its contents are flushed to the table
+ modified, the block is considered <quote>dirty</quote>. In this
+ case, prior to being replaced, its contents are flushed to the table
index from which it came.
</para>
<para>
- Usually the server follows an LRU (Least Recently Used)
- strategy: When choosing a block for replacement, it selects the
- least recently used index block. To be able to make such a
- choice easy, the key cache module maintains a special queue (LRU
- chain) of all used blocks. When a block is accessed, it is
- placed at the end of the queue. When blocks need to be replaced,
- blocks at the beginning of the queue are the least recently used
- and become the first candidates for eviction.
+ Usually the server follows an <firstterm>LRU (Least Recently
+ Used)</firstterm> strategy: When choosing a block for
+ replacement, the least recently used index block is selected. To
+ make such a choice easier, the key cache module maintains a
+ special queue (known as an <firstterm>LRU chain</firstterm>) of
+ all used blocks. When a block is accessed, it is placed at the
+ end of the queue. When blocks need to be replaced, blocks at the
+ beginning of the queue are the least recently used and become
+ the first candidates for eviction.
</para>
<section id="shared-key-cache">
@@ -6373,18 +6375,10 @@
<title id='title-shared-key-cache'>&title-shared-key-cache;</title>
<para>
- Prior to MySQL 4.1, access to the key cache is serialized: No
- two threads can access key cache buffers simultaneously. The
- server processes a request for an index block only after it
- has finished processing the previous request. As a result, a
- request for an index block not present in any key cache buffer
- blocks access by other threads while a buffer is being updated
- to contain the requested index block.
- </para>
-
- <para>
- Starting from version 4.1.0, the server supports shared access
- to the key cache:
+ In older versions of MySQL, access to the key cache was
+ serialized, and no two threads could access key cache buffers
+ simultaneously. However, in MySQL 5.0, the server supports
+ shared access to the key cache:
</para>
<itemizedlist>
@@ -6431,18 +6425,18 @@
not eliminate contention among threads entirely. They still
compete for control structures that manage access to the key
cache buffers. To reduce key cache access contention further,
- MySQL 4.1.1 offers the feature of multiple key caches. This
- allows you to assign different table indexes to different key
- caches.
+ MySQL 5.0 also provides multiple key caches, which allow you
+ to assign different table indexes to different key caches.
</para>
<para>
- When there can be multiple key caches, the server must know
+ Where there are multiple key caches, the server must know
which cache to use when processing queries for a given
<literal>MyISAM</literal> table. By default, all
<literal>MyISAM</literal> table indexes are cached in the
default key cache. To assign table indexes to a specific key
- cache, use the <literal>CACHE INDEX</literal> statement.
+ cache, use the <literal>CACHE INDEX</literal> statement (see
+ <xref linkend="cache-index"/>).
</para>
<para>
@@ -6464,14 +6458,6 @@
</programlisting>
<para>
- <emphasis role="bold">Note</emphasis>: If the server has been
- built with the <literal>ISAM</literal> storage engine enabled,
- <literal>ISAM</literal> tables use the key cache mechanism.
- However, <literal>ISAM</literal> indexes use only the default
- key cache and cannot be reassigned to a different cache.
- </para>
-
- <para>
The key cache referred to in a <literal>CACHE INDEX</literal>
statement can be created by setting its size with a
<literal>SET GLOBAL</literal> parameter setting statement or
@@ -6517,25 +6503,27 @@
<listitem>
<para>
- A hot key cache that takes up 20% of the space allocated
- for all key caches. This is used for tables that are
- heavily used for searches but that are not updated.
+ A <quote>hot</quote> key cache that takes up 20% of the
+ space allocated for all key caches. Use this for tables
+ that are heavily used for searches but that are not
+ updated.
</para>
</listitem>
<listitem>
<para>
- A cold key cache that takes up 20% of the space allocated
- for all key caches. This is used for medium-sized
- intensively modified tables, such as temporary tables.
+ A <quote>cold</quote> key cache that takes up 20% of the
+ space allocated for all key caches. Use this cache for
+ medium-sized, intensively modified tables, such as
+ temporary tables.
</para>
</listitem>
<listitem>
<para>
- A warm key cache that takes up 60% of the key cache space.
- This is the default key cache, to be used by default for
- all other tables.
+ A <quote>warm</quote> key cache that takes up 60% of the
+ key cache space. Employ this as the default key cache, to
+ be used by default for all other tables.
</para>
</listitem>
| Thread |
|---|
| • bk commit - mysqldoc@docsrva tree (jon:1.3111) | jon | 23 Jul |
