Optimization and Indexes
692
• The
optimizer_prune_level
[480]
variable tells the optimizer to skip certain plans based
on estimates of the number of rows accessed for each table. Our experience shows that this
kind of “educated guess” rarely misses optimal plans, and may dramatically reduce query
compilation times. That is why this option is on (
optimizer_prune_level=1
) by default.
However, if you believe that the optimizer missed a better query plan, this option can be switched
off (
optimizer_prune_level=0
) with the risk that query compilation may take much longer. Note
that, even with the use of this heuristic, the optimizer still explores a roughly exponential number of
plans.
• The
optimizer_search_depth
[481]
variable tells how far into the “future” of each incomplete
plan the optimizer should look to evaluate whether it should be expanded further. Smaller values
of
optimizer_search_depth
[481]
may result in orders of magnitude smaller query compilation
times. For example, queries with 12, 13, or more tables may easily require hours and even days to
compile if
optimizer_search_depth
[481]
is close to the number of tables in the query. At the
same time, if compiled with
optimizer_search_depth
[481]
equal to 3 or 4, the optimizer may
compile in less than a minute for the same query. If you are unsure of what a reasonable value is for
optimizer_search_depth
[481]
, this variable can be set to 0 to tell the optimizer to determine the
value automatically.
8.5. Optimization and Indexes
8.5.1. Column Indexes
All MySQL data types can be indexed. Use of indexes on the relevant columns is the best way to
improve the performance of
SELECT
operations.
The maximum number of indexes per table and the maximum index length is defined per storage
engine. See
Chapter 14, Storage Engines
. All storage engines support at least 16 indexes per table
and a total index length of at least 256 bytes. Most storage engines have higher limits.
With
col_name(N)
syntax in an index specification, you can create an index that uses only the first
N
characters of a string column. Indexing only a prefix of column values in this way can make the index
file much smaller. When you index a
BLOB
or
TEXT
column, you must specify a prefix length for the
index. For example:
CREATE TABLE test (blob_col BLOB, INDEX(blob_col(10)));
Prefixes can be up to 1000 bytes long (767 bytes for
InnoDB
tables). Note that prefix limits are
measured in bytes, whereas the prefix length in
CREATE TABLE
statements is interpreted as number
of characters. Be sure to take this into account when specifying a prefix length for a column that uses a
multi-byte character set.
You can also create
FULLTEXT
indexes. These are used for full-text searches. Only the
MyISAM
storage engine supports
FULLTEXT
indexes and only for
CHAR
,
VARCHAR
, and
TEXT
columns. Indexing
always takes place over the entire column and column prefix indexing is not supported. For details, see
Section 12.9, “Full-Text Search Functions”
.
You can also create indexes on spatial data types. Currently, only
MyISAM
supports R-tree indexes on
spatial types. As of MySQL 5.0.16, other storage engines use B-trees for indexing spatial types (except
for
ARCHIVE
and
NDBCLUSTER
, which do not support spatial type indexing).
The
MEMORY
storage engine uses
HASH
indexes by default, but also supports
BTREE
indexes.
8.5.2. Multiple-Column Indexes
MySQL can create composite indexes (that is, indexes on multiple columns). An index may consist
of up to 16 columns. For certain data types, you can index a prefix of the column (see
Section 8.5.1,
“Column Indexes”
).
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...